Geek Answers Handbook

Java beginners need help connecting to the database

I am new to Java and even new to connecting to a Java database. I managed to create a database connection and query the table when I put it in the Main class. Now that I have moved it to the new Connection class, I get errors:

package lokate; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.sql.ResultSet; public class Connection { private static Statement stmt = null; private static ResultSet rs = null; private static Connection con = null; public Connection() throws SQLException { try { Class.forName("com.mysql.jdbc.Driver"); String connectionUrl = "jdbc:mysql://localhost:3306/Lokate?" + "user=root&password="; con = DriverManager.getConnection(connectionUrl); stmt = con.createStatement(); retriveData("SELECT * FROM Users"); int rowsEffected = 0; } catch (SQLException sqlEx) { System.out.println("SQL Exception: "+ sqlEx.toString()); } catch (ClassNotFoundException classEx) { System.out.println("Class Not Found Exception: "+ classEx.toString()); } catch (Exception Ex) { System.out.println("Exception: "+ Ex.toString()); } } public static void retriveData(String SQL) throws Exception { rs = stmt.executeQuery(SQL); while (rs.next()) { System.out.println(rs.getString("fname") + " : " + rs.getString("lname")); } } }

I get an error, I can not find the character. Symbol: createStatement () method and incomparable types for con = DriveManager .....

Can anyone help?

Also, is it better to establish a connection in this class and then call a new object every time I want to do something with db?

Hi,

Billy

+4
source share
4 answers

I would say that your code is an example of many worst practices. Let me figure out the ways:

  • Your Connection class is a bad abstraction that contains nothing but java.sql.Connection.
  • If you use your class, you can never use the connection pool.
  • You firmly connect your driver class, the URL of your connection, etc. You cannot change it without editing and recompiling. The best solution would be to externalize such things.
  • Printing error messages in catch blocks has much less information than providing the entire stack trace.
  • Your code hurts me. It does not comply with Sun Java coding standards.
  • Your retrieveData method is completely useless. What will you do with all these print statements? Wouldn't it be better to load them into a data structure or object so that the rest of your code can use this information?
  • This is rowsAffected - "influences" is a verb, "effect" is a noun. Another variable that is not useful.

You're wrong. Rethink it.

I think you will find this code more useful.

 package persistence; import java.sql.*; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class DatabaseUtils { public static Connection createConnection(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException { Class.forName(driver); if ((username == null) || (password == null) || (username.trim().length() == 0) || (password.trim().length() == 0)) { return DriverManager.getConnection(url); } else { return DriverManager.getConnection(url, username, password); } } public static void close(Connection connection) { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } public static void close(Statement st) { try { if (st != null) { st.close(); } } catch (SQLException e) { e.printStackTrace(); } } public static void close(ResultSet rs) { try { if (rs != null) { rs.close(); } } catch (SQLException e) { e.printStackTrace(); } } public static void rollback(Connection connection) { try { if (connection != null) { connection.rollback(); } } catch (SQLException e) { e.printStackTrace(); } } public static List<Map<String, Object>> map(ResultSet rs) throws SQLException { List<Map<String, Object>> results = new ArrayList<Map<String, Object>>(); try { if (rs != null) { ResultSetMetaData meta = rs.getMetaData(); int numColumns = meta.getColumnCount(); while (rs.next()) { Map<String, Object> row = new HashMap<String, Object>(); for (int i = 1; i <= numColumns; ++i) { String name = meta.getColumnName(i); Object value = rs.getObject(i); row.put(name, value); } results.add(row); } } } finally { close(rs); } return results; } }
+6
source

Your problem is that DriverManager.getConnection returns DriverManager.getConnection . Since your class is also called Connection, you get a name clash between lokate.Connection and lokate.Connection . You will need to specify the full name of the class, wherever you want to use java.sql.Connection , otherwise lokate.Connection assumed.

Specify the fully qualified class name, for example:

 java.sql.Connection con = null; // .... con = DriverManager.getConnection(connectionUrl);

Alternatively, rename the Connection class to something else and you will not get this name conflict.

+3
source

Connection is an existing type in the java.sql package that returns DriverManager.getConnection . You also named your class as Connection , so this causes confusion. The easiest way is to rename your class to something else and add import java.sql.Connection; up.

+1
source

Also, is it better to establish a connection in this class and then call a new object every time I want to do something with db?

I think it would be best practice to use an existing solution to this problem so that you can avoid re-creating the wheel and focus on what makes your problem unique.

If you are writing a server application (it is unclear whether you are), I will also consider using a database connection pool. Creating new database connections on the fly is inefficient and does not scale well. You can read about database connectivity issues in this article, which I wrote at the time .

0
source

More articles:


All Articles