JDBC: Introduction to JDBC (Part II)
程序员文章站
2022-07-13 13:42:40
...
1. Good Practice:
1>Build Connection as late as possible.
2>Close Connection as earyl as possible.
3>Connection Pool can be used to enhance performance.
2. Good Example:
1> JdbcUtil.java
package utils; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public final class JdbcUtil { // Trying to make all secret filed as private. private static String url = "jdbc:mysql://localhost:3306/jdbctest"; private static String username = "root"; private static String password = "root"; // Enable to make sure driver is only registered once. static { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } // Disable user to instantiate JdbcUtil private JdbcUtil() { } public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, username, password); } // Have to judge if a certain field is null public static void releaseResource(ResultSet resultSet, Statement statement, Connection connection) { try { if (null != resultSet) { resultSet.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { if (null != statement) { statement.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { if (null != connection) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } } } }
2>UserDaoImpl.java
package edu.xmu.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import utils.JdbcUtil; import edu.xmu.dao.UserDao; import edu.xmu.domain.User; public class UserDaoImpl implements UserDao { private static Connection connection = null; @Override public void addUser(User user) { PreparedStatement preparedStatement = null; String username = user.getUsername(); String password = user.getPassword(); String sql = "insert into user(user_name, pass_word) values (?, ?)"; try { // 2. Build Connection connection = JdbcUtil.getConnection(); // 3. Create SQL Statement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, username); preparedStatement.setString(2, password); // 4. Execute SQL Statement // 5. If statement is C/U/D, returns integer value indicating rows // affected // If statement is R, returns result set. if (0 <= preparedStatement.executeUpdate()) { System.out.println("Insert succeed!"); } else { System.out.println("Insert failed!"); } } catch (SQLException e) { e.printStackTrace(); } finally { // 6. Release Resources JdbcUtil.releaseResource(null, preparedStatement, connection); } } @Override public User getUser(int id) { PreparedStatement preparedStatement = null; ResultSet resultSet = null; User user = new User(); String sql = "select id, user_name, pass_word from user where id = ?"; try { connection = JdbcUtil.getConnection(); preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, id); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { user.setId(id); user.setUsername(resultSet.getString("user_name")); user.setPassword(resultSet.getString("pass_word")); } } catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtil.releaseResource(resultSet, preparedStatement, connection); } return user; } }