Query a MySQL database with Java

[Geralt]

Hey all, I've connected my database to my java program but I just can't seem to query and compare data to authenticate a user. What am I doing wrong?

 

Application:

btnSubmit.addActionListener(new ActionListener() {			public void actionPerformed (ActionEvent e) {				String name = textField.getText();				String password = textField_1.getText();				String databaseUsername = null;				String databasePassword = null;								DBController db=new DBController();								String SQL = "SELECT name, password FROM users WHERE name='" + name + "' AND password='" + password+ "'";								//queries database				ResultSet rs = db.readRequest(SQL);								//check username and password				try {					while (rs.next()) {					    databaseUsername = rs.getString("name");					    databasePassword = rs.getString("password");					}				} catch (SQLException e1) {					e1.printStackTrace();				}			    if (name.equals(databaseUsername) && password.equals(databasePassword)) {			        lblLoginSucessful.setVisible(true);			    } else {			        lblLoginFailed.setVisible(false);			    }			}		});

Database Controller:

/************************************************************	 * Method Name : readRequest 	 * Input Parameter : String (database query) 	 * Purpose : Obtain the result set from the db query 	 * Return : resultSet (records from the query)	 ************************************************************/	public ResultSet readRequest(String dbQuery) {		ResultSet rs = null;		System.out.println("DB Query: " + dbQuery);		try {			// create a statement object			Statement stmt = con.createStatement();			// execute an SQL query and get the result			rs = stmt.executeQuery(dbQuery);		} catch (Exception e) {			e.printStackTrace();		}		return rs;	}

The error I'm getting is:

 

java.lang.NullPointerException
    at Database.DBController.readRequest(DBController.java:54)
    at Main.ExistingUser$4.actionPerformed(ExistingUser.java:127)

 

Line 54 being "Statement stmt = con.createStatement();" and line 127 being "ResultSet rs = db.readRequest(SQL);"

[SNIT]

What's 'con' in this declaration?:

Statement stmt = con.createStatement();

[Geralt]

 

What's 'con' in this declaration?:

Statement stmt = con.createStatement();

 

Ah forgot to add that. It's from the Database Controller where it's declared as a private variable. I believe it takes it from the import.

 

import import java.sql.Connection;

 

private Connection con;

[SNIT]

Are you actually establishing a connection to the DB?

[Geralt]

Are you actually establishing a connection to the DB?

 

By default the Database Controller should establish it. But I suspected that too and I managed to solve it by coding the database operations within the app class itself.

btnSubmit.addActionListener(new ActionListener() {			public void actionPerformed (ActionEvent e) {				String name = textField.getText();				String password = textField_1.getText();				String databaseUsername = null;				String databasePassword = null;				ResultSet rs = null;								//DBController db=new DBController();								String dbQuery = "SELECT name, password FROM users WHERE name='" + name + "' AND password='" + password+ "'";								//queries database				try {					con = DriverManager.getConnection("jdbc:mysql://localhost:8866/group4project", "ryan", "password");					Statement stmt = con.createStatement();					rs = stmt.executeQuery(dbQuery);				} catch (Exception e1) {					e1.printStackTrace();				}								//check username and password				try {					while (rs.next()) {					    databaseUsername = rs.getString("name");					    databasePassword = rs.getString("password");					}				} catch (SQLException e1) {					e1.printStackTrace();				}			    if (name.equals(databaseUsername) && password.equals(databasePassword)) {			        lblLoginSucessful.setVisible(true);			    } else {			        lblLoginFailed.setVisible(true);			    }			}		});

I still can't figure out how to make it work using the external database controller.

UPDATE: Managed to figure it out. It was the connection after all. Seems like I didn't declare the connection globally in the database controller class.

[SNIT]

----

[Geralt]

-ignore me- LeL

 

Yeah I was pretty puzzled about that question LOL

 

I just created an object of the controller class so I can access the methods within said class. The problem is that I didn't declare the connection as global so I have to manually declare the connection from within each method.

[SNIT]

This is something I found online, this is what your connection should look like. You derive a good concept for a controller from this.

package zetcode;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.logging.Level;import java.util.logging.Logger;public class Version {    public static void main(String[] args) {        Connection con = null;        Statement st = null;        ResultSet rs = null;        String url = "jdbc:mysql://localhost:3306/testdb";        String user = "testuser";        String password = "test623";        try {            con = DriverManager.getConnection(url, user, password);            st = con.createStatement();            rs = st.executeQuery("SELECT VERSION()");            if (rs.next()) {                System.out.println(rs.getString(1));            }        } catch (SQLException ex) {            Logger lgr = Logger.getLogger(Version.class.getName());            lgr.log(Level.SEVERE, ex.getMessage(), ex);        } finally {            try {                if (rs != null) {                    rs.close();                }                if (st != null) {                    st.close();                }                if (con != null) {                    con.close();                }            } catch (SQLException ex) {                Logger lgr = Logger.getLogger(Version.class.getName());                lgr.log(Level.WARNING, ex.getMessage(), ex);            }        }    }}

source

 

 

Basicly, in each method in the controller you should;

 

- Establish the connection

- Create the query

- Execute the query

- Close the connection

[Geralt]

This is something I found online, this is what your connection should look like. You derive a good concept for a controller from this.

 

Basicly, in each method in the controller you should;

 

- Establish the connection

- Create the query

- Execute the query

- Close the connection

 

Here's how my controller looks like.

package Database;import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement;import java.sql.ResultSet; import java.sql.Statement;public class DBController {	private Connection con; 		/********************************************************	 * Method Name : testDriver	 * Input Parameter : nil 	 * Purpose : To test if the driver is properly installed	 * Return :nil	 *******************************************************/	public void testDriver() throws Exception { 		System.out.println("Initializing Server... "); 	try { 		Class.forName("org.gjt.mm.mysql.Driver"); 		System.out.println(" Driver Found."); 	} 	catch (ClassNotFoundException e) { 		System.out.println(" Driver Not Found, exiting.."); 		throw (e); 		} 	} 	public void getConnection() { 		String url = ""; 		try {			url = "jdbc:mysql://localhost:8866/group4project"; //Local Server			//url = "jdbc:mysql://db4free.net:3306/group4project"; //Remote Server (db4free)			//url = "jdbc:mysql://sql6.freesqldatabase.com:3306/group4project"; //Remote Server (FreeSQL)			con = DriverManager.getConnection(url, "ryan", "password"); 			System.out.println("Successfully connected to " + url+ "."); 		} 		catch (java.sql.SQLException e) { 			System.out.println("Connection failed ->"+ url); 			System.out.println(e); 		} 	} 	/************************************************************	 * Method Name : readRequest 	 * Input Parameter : String (database query) 	 * Purpose : Obtain the result set from the db query 	 * Return : resultSet (records from the query)	 ************************************************************/	public ResultSet readRequest(String dbQuery) {		ResultSet rs = null;		Connection con1;		System.out.println("DB Query: " + dbQuery);		try {			// create a statement object			con1 = DriverManager.getConnection("jdbc:mysql://localhost:8866/group4project", "ryan", "password");			Statement stmt = con1.createStatement();			// execute an SQL query and get the result			rs = stmt.executeQuery(dbQuery);		} catch (Exception e) {			e.printStackTrace();		}		return rs;	}	/***********************************************************	 * Method Name : updateRequest 	 * Input Parameter : String (database query) 	 * Purpose : Execute update using the db query 	 * Return : int (count is 1 if successful)	 ***********************************************************/	public int updateRequest(String dbQuery) {		int count = 0;		System.out.println("DB Query: " + dbQuery);		try {			// create a statement object			Statement stmt = con.createStatement();			// execute an SQL query and get the result			count = stmt.executeUpdate(dbQuery);		} catch (Exception e) {			e.printStackTrace();		}		return count;	}	/***********************************************************	 * Method Name : getPreparedStatement 	 * Input Parameter : String (database query) 	 * Purpose : Gets Prepared Statement using the db query 	 * Return : Prepared Statement	 ***********************************************************/	public PreparedStatement getPreparedStatementWithKey(String dbQuery) {		PreparedStatement pstmt = null;		System.out.println("DB prepare statement: " + dbQuery);		try {			// create a statement object						pstmt = con.prepareStatement(dbQuery,Statement.RETURN_GENERATED_KEYS);			} catch (Exception e) {			e.printStackTrace();		}		return pstmt;	}			/***********************************************************	 * Method Name : getPreparedStatement 	 * Input Parameter : String (database query) 	 * Purpose : Gets Prepared Statement using the db query 	 * Return : Prepared Statement	 ***********************************************************/	public PreparedStatement getPreparedStatement(String dbQuery) {		PreparedStatement pstmt = null;		System.out.println("DB prepare statement: " + dbQuery);		try {			// create a statement object							pstmt = con.prepareStatement(dbQuery);		} catch (Exception e) {			e.printStackTrace();		}		return pstmt;	}		/***********************************************************	 * Method Name : terminate 	 * Input Parameter : nil 	 * Purpose : Close db conection 	 * Return :nil	 **********************************************************/	public void terminate() {		// close connection		try {			con.close();			System.out.println("Connection is closed");		} catch (Exception e) {			e.printStackTrace();		}	}	public static void main(String[] arg)throws Exception{		DBController db = new DBController();		db.testDriver();	}}

I didn't establish the connection for all methods in the controller which was why my query wasn't working.

[SNIT]

Sweet, make sure you close the connection in a finally{} at the end of the try catch in each method, not doing so could lead to issues and data loss in the future.

[Geralt]

Sweet, make sure you close the connection in a finally{} at the end of the try catch in each method, not doing so could lead to issues and data loss in the future.

 

Yeah that too. I always seem to forget my closes. >.>