What is the best way / pattern to set up mysql and jdbc connection?
The basic template for MySQL / JDBC looks like this:
Get connection :
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://databaseName");
Follow the instruction :
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * from tableName");
while (rs.next()) {
System.out.println(rs.getString(1));
}
Close 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.
source to share
This is twenty first century - use the JPA implementation (ORM). But if you insist on going back to metal (at the risk of lowering your voices) -
There are many ways to connect JDBC from any driver. The use of reflection with a hard class name is the most common and possibly the most damaged brain. If you intend to reinstall the class name, you can get the benefits of regular code (the compiler catches typos, deals with them without extraneous exceptions, is easier to read, explicit dependencies, better tooling support, etc.).
Also get in the habit of safely clearing resources.
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. Yes, use an ORM. They are very respectable these days.
You don't need to do all of this for every single statement. You don't want to create instantiation scripts every time. In particular, it is useful to use an incident around an idiom.
source to share
Here's the sun documentation for creating a JDBC connection . From there, it's easy to access the Statement object and run simple SQL.
For production level systems, you probably want to create a connection pool as well .
source to share
It depends on your case.
If you just need to execute some requests from a standalone application, you should use one connection, for example:
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 a real application (especially a web application) then use DataSource. Read the DB and Web Server tutorial on how to set up a data source. The DataSource allows pooling to be used to improve performance.
Configuring a DataSource is not an easy process.
source to share
Use Spring Framework JDBC - All you have to do is create an XML context file and use the JDBC template class. Just a few lines of XML + Java code will help you. The advantage is to keep the connection data from the compiled Java. See: http://www.springbyexample.org/examples/simple-spring-jdbc-template.html
source to share