Java连接数据库 #01# JDBC单线程适用
官方教程（包括 javase的基础部分）：JDBC Basics
重新梳理、学习一下“Java连接数据库”相关的内容。
因为最开始没有认真学多线程和JDBC，一直在自己写的多线程程序中维持下面的错误写法：
- 多个线程共用一个connection
- connection只开不关
为什么上述做法是错误的呢？ 可以参看这个帖子。- - “JDBC规范并未规定那三个对象必须是线程安全的，因此所有的JDBC厂商也不会去弄成线程安全的，正因为如此，所以就会有并发问题。” 、-- “ 并不是说不能把连接对象弄成成员变量，只是不能将其弄成成员变量后，在多线程环境下处于共享这些对象，如果同步处理得不好，那就会产生严重的连接泄漏。为了避免这种情况发生，仅在用时获取连接，用完后马上关掉。” -- “如果你对JDBC、多线程编程没有达到非常熟练的程度，还是老老实实地使用经典的JDBC代码结构。” -- 摘抄自csdn 火龙果被占用了
另外，connection只开不关很容易导致连接失效（mysql默认保持连接的时间是8小时，如果这个连接在8小时内无人访问的话，就会关闭这个连接。- -摘）
我把这些错误代码放在第一小节记录下来，作为java连接数据库的最原始版本，在这之后逐渐改良成可以适应各种场景的正确代码。
DROP TABLE IF EXISTS `profile`; CREATE TABLE `profile` ( `profileId` BIGINT(20) NOT NULL AUTO_INCREMENT, `username` VARCHAR(50) NOT NULL, `password` VARCHAR(50) NOT NULL, `nickname` VARCHAR(50) NOT NULL, `last_online` TIMESTAMP NULL DEFAULT NULL, `gender` CHAR(1) NULL DEFAULT NULL, `birthday` TIMESTAMP NULL DEFAULT NULL, `location` VARCHAR(50) NULL DEFAULT NULL, `joined` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`profileId`), UNIQUE INDEX `username` (`username`) ) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;
profile表可以基本等价为user表来理解。
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
package org.sample.entity; import java.sql.Timestamp; public class Profile { private Long profileId; private String username; private String password; private String nickname; private Timestamp last_online; private Character gender; private Timestamp birthday; private String location; private Timestamp joined; public Profile() { } public Long getProfileId() { return profileId; } public void setProfileId(Long profileId) { this.profileId = profileId; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getNickname() { return nickname; } public void setNickname(String nickname) { this.nickname = nickname; } public Timestamp getLast_online() { return last_online; } public void setLast_online(Timestamp last_online) { this.last_online = last_online; } public Character getGender() { return gender; } public void setGender(Character gender) { this.gender = gender; } public Timestamp getBirthday() { return birthday; } public void setBirthday(Timestamp birthday) { this.birthday = birthday; } public String getLocation() { return location; } public void setLocation(String location) { this.location = location; } public Timestamp getJoined() { return joined; } public void setJoined(Timestamp joined) { this.joined = joined; } @Override public String toString() { return "Profile{" + "profileId=" + profileId + ", username='" + username + '\'' + ", password='" + password + '\'' + ", nickname='" + nickname + '\'' + ", last_online=" + last_online + ", gender=" + gender + ", birthday=" + birthday + ", location='" + location + '\'' + ", joined=" + joined + '}'; } }
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
③ ConnectionFactory.java或者常说的Dbutil（错误代码 ↓）
package org.sample.db; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.ResourceBundle; /** * 单线程适用，只开不关，反复用一个 Connection */ public class StaticConnectionFactory { private static ResourceBundle rb = ResourceBundle.getBundle("org.sample.db.db-config"); private static final String JDBC_URL = rb.getString("jdbc.url"); private static final String JDBC_USER = rb.getString("jdbc.username"); private static final String JDBC_PASSWORD = rb.getString("jdbc.password"); private static Connection conn = null; static { try { // Class.forName("org.gjt.mm.mysql.Driver"); // JDBC 4.0 之后（包括 JDBC 4.0）不再需要 class.forName ，详细查看 javaSE6 之后的 API conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD); } catch (SQLException e) { throw new RuntimeException("Error connecting to the database", e); } } public static Connection getConnection() { return conn; } public static void setAutoCommit(boolean autoCommit) throws SQLException { conn.setAutoCommit(autoCommit); } public static void commit() throws SQLException { conn.commit(); } public static void rollback() throws SQLException { conn.rollback(); } }
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
④ org/sample/db/db-config.properties
jdbc.url=jdbc:mysql://***.**.**.**:3306/profiles?characterEncoding=utf8 jdbc.username=root jdbc.password=aaaaaaaaaaa
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
package org.sample.dao; import org.sample.entity.Profile; import java.util.List; public interface ProfileDAO { int saveProfile(Profile profile); List<Profile> listProfileByNickname(String nickname); Profile getProfileByUsername(String username); int updateProfileById(Profile profile); int updatePassword(String username, String password); int updateLastOnline(String username); }
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
⑥ ProfileDAOImpl.java（为了用“带资源的try”严重画蛇添足了。）
package org.sample.dao.impl; import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException; import org.sample.dao.ProfileDAO; import org.sample.db.StaticConnectionFactory; import org.sample.entity.Profile; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; // NotThreadSafe public class ProfileDAOImpl implements ProfileDAO { private static final Connection conn = StaticConnectionFactory.getConnection(); @Override public int saveProfile(Profile profile) { int i = 0; try ( PreparedStatement ps = createPreparedStatementForSaveProfile(conn, profile); ) { i = ps.executeUpdate(); } catch (SQLException e) { if (!(e instanceof MySQLIntegrityConstraintViolationException)) { e.printStackTrace(); } } return i; } @Override public List<Profile> listProfileByNickname(String nickname) { List<Profile> profiles = new ArrayList<>(); try ( PreparedStatement ps = createPreparedStatementForListProfileByNickname(conn, nickname); ResultSet rs = ps.executeQuery(); ) { while (rs.next()) { Profile profile = extractProfileFromResultSet(rs); profiles.add(profile); } } catch (SQLException e) { e.printStackTrace(); } return profiles; } @Override public Profile getProfileByUsername(String username) { Profile profile = null; try ( PreparedStatement ps = createPreparedStatementForGetProfileByUsername(conn, username); ResultSet rs = ps.executeQuery(); ) { if (rs.next()) { profile = extractProfileFromResultSet(rs); } } catch (SQLException e) { e.printStackTrace(); } return profile; } @Override public int updateProfileById(Profile profile) { int i = 0; try ( PreparedStatement ps = createPreparedStatementForUpdateProfileById(conn, profile); ) { i = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } return i; } @Override public int updatePassword(String username, String password) { int i = 0; try ( PreparedStatement ps = createPreparedStatementForUpdatePassword(username, password); ) { i = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } return i; } @Override public int updateLastOnline(String username) { int i = 0; try ( PreparedStatement ps = createPreparedStatementForUpdateLastOnline(username); ) { i = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } return i; } private Profile extractProfileFromResultSet(ResultSet rs) throws SQLException { Profile profile = new Profile(); profile.setBirthday(rs.getTimestamp("birthday")); profile.setJoined(rs.getTimestamp("joined")); profile.setLast_online(rs.getTimestamp("last_online")); profile.setLocation(rs.getString("location")); profile.setNickname(rs.getString("nickname")); profile.setPassword(rs.getString("password")); profile.setProfileId(rs.getLong("profile_id")); profile.setUsername(rs.getString("username")); if (rs.getString("gender") != null) { profile.setGender(rs.getString("gender").charAt(0)); } return profile; } private PreparedStatement createPreparedStatementForSaveProfile(Connection conn, Profile profile) throws SQLException { String sql = "INSERT INTO `profiles`.`profile` (`username`, `password`, `nickname`) " + "VALUES (?, ?, ?)"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, profile.getUsername()); ps.setString(2, profile.getPassword()); ps.setString(3, profile.getNickname()); return ps; } private PreparedStatement createPreparedStatementForListProfileByNickname(Connection conn, String nickname) throws SQLException { String sql = "SELECT `profile_id`, `username`, `password`, `nickname`, `last_online`, `gender`, `birthday`, `location`, `joined`" + "FROM `profiles`.`profile`" + "WHERE `nickname`=?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, nickname); return ps; } private PreparedStatement createPreparedStatementForGetProfileByUsername(Connection conn, String username) throws SQLException { String sql = "SELECT `profile_id`, `username`, `password`, `nickname`, `last_online`, `gender`, `birthday`, `location`, `joined`" + "FROM `profiles`.`profile`" + "WHERE `username`=?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, username); return ps; } private PreparedStatement createPreparedStatementForUpdateProfileById(Connection conn, Profile profile) throws SQLException { String sql = "UPDATE `profiles`.`profile`" + "SET `nickname`=?, `gender`=?, `birthday`=?, `location`=? " + "WHERE `profile_id`=?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, profile.getNickname()); ps.setString(2, profile.getGender() != null ? String.valueOf(profile.getGender()) : null); ps.setTimestamp(3, profile.getBirthday()); ps.setString(4, profile.getLocation()); ps.setLong(5, profile.getProfileId()); return ps; } private PreparedStatement createPreparedStatementForUpdatePassword(String username, String password) throws SQLException { String sql = "UPDATE `profiles`.`profile`" + "SET `password`=? " + "WHERE `username`=?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, password); ps.setString(2, username); return ps; } private PreparedStatement createPreparedStatementForUpdateLastOnline(String username) throws SQLException { String sql = "UPDATE `profiles`.`profile`" + "SET `last_online`=CURRENT_TIMESTAMP " + "WHERE `username`=?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, username); return ps; } }
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
package org.sample.dao; import org.junit.Test; import org.sample.dao.impl.ProfileDAOImpl; import org.sample.db.StaticConnectionFactory; import org.sample.entity.Profile; import java.sql.SQLException; import java.util.List; import static org.junit.Assert.*; public class ProfileDAOTest { private ProfileDAO profileDAO = new ProfileDAOImpl(); private static final String USER_NAME = "hello123"; @Test public void saveProfile() { Profile profile = new Profile(); profile.setUsername(USER_NAME); profile.setPassword("231231232"); profile.setNickname("jack"); int i = profileDAO.saveProfile(profile); System.out.println(i); } @Test public void listProfileByNickname() { List<Profile> profiles = profileDAO.listProfileByNickname("123"); } @Test public void getProfileByUsername() { Profile existProfile = profileDAO.getProfileByUsername(USER_NAME); Profile notExistProfile = profileDAO.getProfileByUsername(USER_NAME + "321"); assertNotNull(existProfile); assertNull(notExistProfile); } @Test public void updateProfileById() { Profile profile = profileDAO.getProfileByUsername(USER_NAME); int i = profileDAO.updateProfileById(profile); assertEquals(1, i); // 即便没改变值，但是还是会重新set一遍，因此影响行数还是一行 profile.setGender('f'); profile.setNickname("www" + Math.random()); int j = profileDAO.updateProfileById(profile); assertEquals(1, j); } @Test public void updatePassword() { profileDAO.updatePassword(USER_NAME, "www" + Math.random()); } @Test public void updateLastOnline() throws SQLException { try { StaticConnectionFactory.setAutoCommit(false); profileDAO.getProfileByUsername(USER_NAME); profileDAO.updateLastOnline(USER_NAME); StaticConnectionFactory.commit(); } catch (SQLException e) { e.printStackTrace(); try { StaticConnectionFactory.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } } finally { StaticConnectionFactory.setAutoCommit(true); } } }
