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

0 votes
asked May 4, 2009 by feiroox

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

6 Answers

0 votes
answered Jan 4, 2009 by chris

Her is a very small sample which illustrates it:

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

0 votes
answered Jan 4, 2009 by sualeh-fatehi

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

0 votes
answered May 4, 2009 by vinnie

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.

0 votes
answered May 4, 2009 by roman

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.

0 votes
answered May 4, 2009 by yuval-adam

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.

0 votes
answered May 4, 2009 by tom-hawtin-tackline

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.

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...