General Interests & Hobbies > Java Programming

JDBC - Any knowledge is welcome...

Pages: <<< (2/3) >>>

Pwnd:

Its really simple, to be honest...  Perhaps you are overanalyzing the SQL syntax.  Anything in particular you're having trouble with? 

t4:

sounds like you don't understand SQL (or a specific dialect)

RiiPiiNFtW:

Well, I am working with a prewritten system and I added a field within the table for MAC Addresses to be stored. Upon login, the DB is read, and here is what I am having trouble understanding.

                               
--- Code: SQL ---bldr.append("SELECT UNIX_TIMESTAMP() as now, id, type, UNIX_TIMESTAMP(expire) as expire ");                                bldr.append("FROM sanctions ");                                bldr.append("WHERE ((name = ?) ");                                bldr.append("OR (ipaddress IS NOT NULL AND ipaddress = ?)) ");                                bldr.append("AND closed = 0 ");                                bldr.append("ORDER BY id DESC");                                statement = connection.prepareStatement(bldr.toString());                                statement.setString(1, username);                                statement.setString(2, address);                                ResultSet RESULT = statement.executeQuery(); // blocking op
I am trying to get the syntax to read the MAC address within this and I have tried several different things like:


--- Code: SQL ---bldr.append("SELECT UNIX_TIMESTAMP() as now, id, type, UNIX_TIMESTAMP(expire) as expire ");                                bldr.append("FROM sanctions ");                                bldr.append("WHERE ((name = ?) ");                                bldr.append("OR (ipaddress IS NOT NULL AND ipaddress = ?) OR (macaddress IS NOT NULL AND macaddress =?)) ");                                bldr.append("AND closed = 0 ");                                bldr.append("ORDER BY id DESC");                                statement = connection.prepareStatement(bldr.toString());                                statement.setString(1, username);                                statement.setString(2, address);                                ResultSet RESULT = statement.executeQuery(); // blocking op
or this


--- Code: SQL ---bldr.append("SELECT UNIX_TIMESTAMP() as now, id, type, UNIX_TIMESTAMP(expire) as expire ");                                bldr.append("FROM sanctions ");                                bldr.append("WHERE ((name = ?) ");                                bldr.append("AND (ipaddress IS NOT NULL AND ipaddress = ?) OR (macaddress IS NOT NULL AND macaddress = ?)) ");                                bldr.append("AND closed = 0 ");                                bldr.append("ORDER BY id DESC");                                statement = connection.prepareStatement(bldr.toString());                                statement.setString(1, username);                                statement.setString(2, address);                                ResultSet RESULT = statement.executeQuery(); // blocking op
But I feel like I am using AND wrong since it is already being used afterwards.

Perhaps I am over analyzing the syntax as I am know to do such things, just still difficult to understand.

Pwnd:

You have to set its value by using setString, for a start. 

Bowser jr:

I don't know if this will help you, but I have written a quick and easy wrapper class for JDBC purposes. Change the stuff you find necessary to change.


--- Code: Java ---import java.sql.Connection;import java.sql.Driver;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement; import javax.sql.DataSource; import com.mysql.jdbc.PreparedStatement;   public class MySQLConnector {         static Connection con;                public static void init() {                // TODO Auto-generated constructor stub                                                try {                        Class.forName("com.mysql.jdbc.Driver");                        con = DriverManager.getConnection("jdbc:mysql://localhost/timelister?","root","");                                        } catch (SQLException e) {                        // TODO Auto-generated catch block                        e.printStackTrace();                } catch (ClassNotFoundException e) {                        // TODO Auto-generated catch block                        e.printStackTrace();                }                       }                public static ResultSet get(String s, Object[] objects){                PreparedStatement statement;                try {                        statement = (PreparedStatement) con.prepareStatement(s);                        for(int i = 0; i < objects.length; i++){                                                                statement.setObject(i + 1, objects[i]);                        }                         return statement.executeQuery();                                                            } catch (SQLException e) {                        // TODO Auto-generated catch block                        e.printStackTrace();                }                return null;        }                public static ResultSet send(String s, Object[] objects){                PreparedStatement statement;                try {                        statement = (PreparedStatement) con.prepareStatement(s);                        for(int i = 0; i < objects.length; i++){                                statement.setObject(i + 1, objects[i]);                        }                         statement.execute();                                                            } catch (SQLException e) {                        // TODO Auto-generated catch block                        e.printStackTrace();                }                return null;        }                        public static ResultSet send(String s){                PreparedStatement statement;                try {                        statement = (PreparedStatement) con.prepareStatement(s);                        statement.execute();                                                            } catch (SQLException e) {                        // TODO Auto-generated catch block                        e.printStackTrace();                }                return null;        }} You want to use the get method. The parameters you exchange with "?" is just an array of objects. Just make sure to have as many parameters as "?"s.

Pages: <<< (2/3) >>>

Go to full version