官方教程(包括 javase的基础部分)：JDBC Basics
重新梳理、学习一下“Java连接数据库”相关的内容。
因为最开始没有认真学多线程和JDBC，一直在自己写的多线程程序中维持下面的错误写法：
多个线程共用一个connection
connection只开不关
为什么上述做法是错误的呢？ 可以参看这个帖子。- - “JDBC规范并未规定那三个对象必须是线程安全的，因此所有的JDBC厂商也不会去弄成线程安全的，正因为如此，所以就会有并发问题。” 、-- “ 并不是说不能把连接对象弄成成员变量，只是不能将其弄成成员变量后，在多线程环境下处于共享这些对象，如果同步处理得不好，那就会产生严重的连接泄漏。为了避免这种情况发生，仅在用时获取连接，用完后马上关掉。” -- “如果你对JDBC、多线程编程没有达到非常熟练的程度，还是老老实实地使用经典的JDBC代码结构。” -- 摘抄自csdn 火龙果被占用了
另外，connection只开不关很容易导致连接失效(mysql默认保持连接的时间是8小时，如果这个连接在8小时内无人访问的话，就会关闭这个连接。- -摘)
我把这些错误代码放在第一小节记录下来，作为java连接数据库的最原始版本，在这之后逐渐改良成可以适应各种场景的正确代码。
① DDL.sql
DROP TABLE IF EXISTS`profile`;CREATE TABLE`profile` (
`profileId`BIGINT(20) NOT NULLAUTO_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表来理解。
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
② 对应的实体类Profile.java
packageorg.sample.entity;importjava.sql.Timestamp;public classProfile {privateLong profileId;privateString username;privateString password;privateString nickname;privateTimestamp last_online;privateCharacter gender;privateTimestamp birthday;privateString location;privateTimestamp joined;publicProfile() {
}publicLong getProfileId() {returnprofileId;
}public voidsetProfileId(Long profileId) {this.profileId =profileId;
}publicString getUsername() {returnusername;
}public voidsetUsername(String username) {this.username =username;
}publicString getPassword() {returnpassword;
}public voidsetPassword(String password) {this.password =password;
}publicString getNickname() {returnnickname;
}public voidsetNickname(String nickname) {this.nickname =nickname;
}publicTimestamp getLast_online() {returnlast_online;
}public voidsetLast_online(Timestamp last_online) {this.last_online =last_online;
}publicCharacter getGender() {returngender;
}public voidsetGender(Character gender) {this.gender =gender;
}publicTimestamp getBirthday() {returnbirthday;
}public voidsetBirthday(Timestamp birthday) {this.birthday =birthday;
}publicString getLocation() {returnlocation;
}public voidsetLocation(String location) {this.location =location;
}publicTimestamp getJoined() {returnjoined;
}public voidsetJoined(Timestamp joined) {this.joined =joined;
}
@OverridepublicString 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(错误代码 ↓)
packageorg.sample.db;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.SQLException;importjava.util.ResourceBundle;/*** 单线程适用，只开不关，反复用一个 Connection*/
public classStaticConnectionFactory {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 staticConnection getConnection() {returnconn;
}public static void setAutoCommit(boolean autoCommit) throwsSQLException {
conn.setAutoCommit(autoCommit);
}public static void commit() throwsSQLException {
conn.commit();
}public static void rollback() throwsSQLException {
conn.rollback();
}
}
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
④ org/sample/db/db-config.properties
jdbc.url=jdbc:mysql://***.**.**.**:3306/profiles?characterEncoding=utf8
jdbc.username=root
jdbc.password=aaaaaaaaaaa
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
⑤ ProfileDAO.java
packageorg.sample.dao;importorg.sample.entity.Profile;importjava.util.List;public interfaceProfileDAO {intsaveProfile(Profile profile);
ListlistProfileByNickname(String nickname);
Profile getProfileByUsername(String username);intupdateProfileById(Profile profile);intupdatePassword(String username, String password);intupdateLastOnline(String username);
}
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
⑥ ProfileDAOImpl.java(为了用“带资源的try”严重画蛇添足了。)
packageorg.sample.dao.impl;importcom.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;importorg.sample.dao.ProfileDAO;importorg.sample.db.StaticConnectionFactory;importorg.sample.entity.Profile;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.List;//NotThreadSafe
public class ProfileDAOImpl implementsProfileDAO {private static final Connection conn =StaticConnectionFactory.getConnection();
@Overridepublic intsaveProfile(Profile profile) {int i = 0;try(
PreparedStatement ps=createPreparedStatementForSaveProfile(conn, profile);
) {
i=ps.executeUpdate();
}catch(SQLException e) {if (!(e instanceofMySQLIntegrityConstraintViolationException)) {
e.printStackTrace();
}
}returni;
}
@Overridepublic ListlistProfileByNickname(String nickname) {
List 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();
}returnprofiles;
}
@OverridepublicProfile 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();
}returnprofile;
}
@Overridepublic intupdateProfileById(Profile profile) {int i = 0;try(
PreparedStatement ps=createPreparedStatementForUpdateProfileById(conn, profile);
) {
i=ps.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}returni;
}
@Overridepublic intupdatePassword(String username, String password) {int i = 0;try(
PreparedStatement ps=createPreparedStatementForUpdatePassword(username, password);
) {
i=ps.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}returni;
}
@Overridepublic intupdateLastOnline(String username) {int i = 0;try(
PreparedStatement ps=createPreparedStatementForUpdateLastOnline(username);
) {
i=ps.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}returni;
}private Profile extractProfileFromResultSet(ResultSet rs) throwsSQLException {
Profile profile= newProfile();
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));
}returnprofile;
}private PreparedStatement createPreparedStatementForSaveProfile(Connection conn, Profile profile) throwsSQLException {
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());returnps;
}private PreparedStatement createPreparedStatementForListProfileByNickname(Connection conn, String nickname) throwsSQLException {
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);returnps;
}private PreparedStatement createPreparedStatementForGetProfileByUsername(Connection conn, String username) throwsSQLException {
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);returnps;
}private PreparedStatement createPreparedStatementForUpdateProfileById(Connection conn, Profile profile) throwsSQLException {
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());returnps;
}private PreparedStatement createPreparedStatementForUpdatePassword(String username, String password) throwsSQLException {
String sql= "UPDATE `profiles`.`profile`" +
"SET `password`=? " +
"WHERE `username`=?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, password);
ps.setString(2, username);returnps;
}private PreparedStatement createPreparedStatementForUpdateLastOnline(String username) throwsSQLException {
String sql= "UPDATE `profiles`.`profile`" +
"SET `last_online`=CURRENT_TIMESTAMP " +
"WHERE `username`=?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, username);returnps;
}
}
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
⑦ ProfileDAOTest.java
packageorg.sample.dao;importorg.junit.Test;importorg.sample.dao.impl.ProfileDAOImpl;importorg.sample.db.StaticConnectionFactory;importorg.sample.entity.Profile;importjava.sql.SQLException;importjava.util.List;import static org.junit.Assert.*;public classProfileDAOTest {private ProfileDAO profileDAO = newProfileDAOImpl();private static final String USER_NAME = "hello123";
@Testpublic voidsaveProfile() {
Profile profile= newProfile();
profile.setUsername(USER_NAME);
profile.setPassword("231231232");
profile.setNickname("jack");int i =profileDAO.saveProfile(profile);
System.out.println(i);
}
@Testpublic voidlistProfileByNickname() {
List profiles = profileDAO.listProfileByNickname("123");
}
@Testpublic voidgetProfileByUsername() {
Profile existProfile=profileDAO.getProfileByUsername(USER_NAME);
Profile notExistProfile= profileDAO.getProfileByUsername(USER_NAME + "321");
assertNotNull(existProfile);
assertNull(notExistProfile);
}
@Testpublic voidupdateProfileById() {
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);
}
@Testpublic voidupdatePassword() {
profileDAO.updatePassword(USER_NAME,"www" +Math.random());
}
@Testpublic void updateLastOnline() throwsSQLException {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);
}
}
}
