本blog提供了一个简单的通过JDBC驱动建立JDBC连接例程。并分别通过Statement和PreparedStatement实现对数据库的查询。
在下一篇blog中将重点比較Statement与PreparedStatement的差异。
1、为项目加入JDBC驱动
1）JDBC驱动下载
官方下载地址：mysql-connector-java-5.0.8.zip
CSDN资料下载地址：mysql-connector-java-5.0.8.zip
2）为项目加入JDBC驱动
建立项目Java项目JDBCDemo,并在JDBCDemo项目中建立一个lib目录，将驱动文件复制到lib目录，选中驱动文件，右键->BuildPath->Add To Build Path;如图所看到的：
2、建立db_bbs数据库
1）构建一个数据库db_bbs;
2）运行db_bbs.sql文件的sql语句。在db_bbs数据库中创建user表。并加入数据;
SET FOREIGN_KEY_CHECKS=0; -- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`password` varchar(10) NOT NULL,
`gender` varchar(1) NOT NULL,
`regtime` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gb2312; -- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'andy', 'andy', '1', '2014-05-13 17:33:28');
INSERT INTO `user` VALUES ('2', 'jack', 'jack', '1', '2014-05-14 17:33:55');
INSERT INTO `user` VALUES ('3', 'rose', 'rose', '0', '2014-05-13 17:34:36');
3、通过属性文件配置数据库
1）属性配置文件db.properties;
#mysql DB properties
DB_DRIVER_CLASS=com.mysql.jdbc.Driver
DB_URL=jdbc:mysql://localhost:3306/db_bbs
DB_USERNAME=root
DB_PASSWORD=root #Oracle DB Properties
#DB_DRIVER_CLASS=oracle.jdbc.driver.OracleDriver
#DB_URL=jdbc:oracle:thin:@localhost:1571:db_bbs
#DB_USERNAME=scott
#DB_PASSWORD=tiger
2）将属性配置文件加入到项目的根文件夹;
4、建立JDBC连接
package com.andieguo.jdbc; import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties; /**
* 该类封装了连接和关闭数据库连接操作
*
* @author andieguo
*
*/
public class DBConnection { public static Connection getConnection() {
Properties props = new Properties();
FileInputStream fis = null;
Connection con = null;
try {
fis = new FileInputStream("db.properties");
props.load(fis);
// 载入驱动
Class.forName(props.getProperty("DB_DRIVER_CLASS"));
// 创建一个连接
con = DriverManager.getConnection(props.getProperty("DB_URL"), props.getProperty("DB_USERNAME"), props.getProperty("DB_PASSWORD"));
} catch (IOException | SQLException | ClassNotFoundException e) {
e.printStackTrace();
}
return con;
} // 关闭ResultSet
public static void closeResultSet(ResultSet rs) {
if (rs != null) {
try {
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
} // 关闭Statement
public static void closeStatement(Statement stm) {
if (stm != null) {
try {
stm.close();
stm = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
} // 关闭PreparedStatement
public static void closePreparedStatement(PreparedStatement pstm) {
if (pstm != null) {
try {
pstm.close();
pstm = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
} // 关闭Connection
public static void closeConnection(Connection con) {
if (con != null) {
try {
con.close();
con = null;
} catch (SQLException e) {
e.printStackTrace();
}
con = null;
}
} }
5、使用Statement进行查询
package com.andieguo.jdbc; import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List; public class DBHelper { public static void queryAllByStatement() {
List<User> users = new ArrayList<User>();
Connection conn = DBConnection.getConnection();
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from user");
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setGender(rs.getBoolean("gender"));
user.setRegtime(rs.getDate("regtime"));
System.out.println(user.toString());
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBConnection.closeResultSet(rs);
DBConnection.closeStatement(stmt);
DBConnection.closeConnection(conn);
}
} public static void queryById(Integer id) {
Connection conn = DBConnection.getConnection();
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from user where id = " + id);
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setGender(rs.getBoolean("gender"));
user.setRegtime(rs.getDate("regtime"));
System.out.println(user.toString());
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBConnection.closeResultSet(rs);
DBConnection.closeStatement(stmt);
DBConnection.closeConnection(conn);
}
} }
6、使用PreparedStatement进行查询
package com.andieguo.jdbc; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List; public class DBHelper { public static void queryAllByprepareStatement() {
List<User> users = new ArrayList<User>();
Connection conn = DBConnection.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement("select * from user");
rs = ps.executeQuery();
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setGender(rs.getBoolean("gender"));
user.setRegtime(rs.getDate("regtime"));
System.out.println(user.toString());
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBConnection.closeResultSet(rs);
DBConnection.closeStatement(ps);
DBConnection.closeConnection(conn);
}
} public static void queryPrepareById(Integer id) {
Connection conn = DBConnection.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement("select * from user where id = ?");
ps.setInt(1, id);// 设置占位符參数
rs = ps.executeQuery();
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setGender(rs.getBoolean("gender"));
user.setRegtime(rs.getDate("regtime"));
System.out.println(user.toString());
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBConnection.closeResultSet(rs);
DBConnection.closeStatement(ps);
DBConnection.closeConnection(conn);
}
} }
7、測试用例
package com.andieguo.jdbc;
import junit.framework.TestCase;
public class DBHelperTest extends TestCase {
public void getConnectionTest(){
System.out.println(DBConnection.getConnection());
}
public void queryAllByStatementTest(){
DBHelper.queryAllByStatement();
}
public void queryAllByprepareStatementTest(){
DBHelper.queryAllByprepareStatement();
}
public void queryByIdTest(){
DBHelper.queryById(2);
}
public void queryByPrepareIdTest(){
DBHelper.queryPrepareById(3);
}
}
8、參考
JDBC Example Tutorial – Drivers, Connection, Statement and ResultSet（推荐）
Java code for connecting Mysql database and using Arraylist type