What is the best way/template to set up connection mysql and jdbc?

Question

What is the best way to set up connection with mysql's jdbc? And execute simple statement. How to do that? Thank you.

Answer 1

Her is a very small smaple which illustrates it:

http://web.njit.edu/all_topics/Servers/MySQL/Docs/mySample.java.html

Answer 2

Here's the sun documentation for creating a JDBC connection. From there it's easy to get access to a Statement object and run some simple SQL.

For production level systems you'll probably also want to create a connection pool.

Answer 3

The basic boilerplate for MySQL/JDBC goes something like this:

Get the connection:

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://databaseName");

Execute the statement:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * from tableName");
while (rs.next()) {
 System.out.println(rs.getString(1));
}

Close the statement and connection:

rs.close();
stmt.close();
conn.close();

You just need to make sure you have the driver installed and/or in your CLASSPATH.

Answer 4

It depends on your case.

If you simply need to execute some queries from standalone application then you should use single connection like:

Class.forName ("yourDriverName");

Connection cn = DriverManager.getConnection ("db url");
Statement st = cn.createStatement ();
ResultSet rs = st.executeQuery ("select * from foo");
while (rs.next()) {
  doSmth ();
} 
rs.close ();
st.close ();
cn.close ();

But if you are developing real application (specially web-application) then use DataSource's. Read manual of your DB and Web-server how to configure datasource. DataSource allows you to use connection-pooling - it'll nessecary to increase performance.

Configuring DataSource isn't difficult process.

Answer 5

Use Spring Framework's JDBC abstraction framework - all you need to do is create a context XML file, and use the JDBC template class. Just a few lines of XML + Java code will get you going. The advantage is keeping your connection details out of compiled Java. See: http://www.springbyexample.org/examples/simple-spring-jdbc-template.html

Answer 6

This is the twenty first century - use a JPA (ORM) implementation. But if you insist on going back to the metal (at the risk of down votes) -

There are many ways of getting a JDBC connection from some driver. Using reflection with a hardwired class name is the commonest and perhaps most brain damaged. If you're going to hardwire a class name, you might as well as get the benefits of normal code (compiler catches typos, no extraneous exceptions to deal with, easier to read, explicit dependencies, better tool support, etc).

Also get in to the habit of clearing up resources safely.

So:

public static void main(String[] args) throws SQLException {
     Driver driver = new com.mysql.jdbc.Driver();
     Connection connection = driver.connect(
         "jdbc:mysql://mydatabase", 
         new java.util.Properties() {{
              put("user", "fred");
         }}
     );
     try {
         PreparedStatement statement = connection.prepareStatement(
             "SELECT insideLeg FROM user WHERE name=?"
         );
         try {
             statement.setString(1, "jim");
             ResultSet results = statement.executeQuery();
             try {
                 if (results.next() {
                     System.out.println("= "+results.getLong(1));
                 } else {
                     System.out.println("Missing.");
                 } 
             } finally {
                 results.close();
             }
         } finally {
             statement.close();
         }
     } finally {
         connection.close();
     }
}

What a mess! And it doesn't even use transactions yet. Yes, use an ORM. They're very respectable these days.

You wont need to do all that for every single statement. You don't want to go around creating instantiating drivers every time. In particular the execute around idiom is useful.