本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', '-05-13 17:33:28');INSERT INTO `user` VALUES ('2', 'jack', 'jack', '1', '-05-14 17:33:55');INSERT INTO `user` VALUES ('3', 'rose', 'rose', '0', '-05-13 17:34:36');
3、通过属性文件配置数据库
1）属性配置文件db.properties;
#mysql DB propertiesDB_DRIVER_CLASS=com.mysql.jdbc.DriverDB_URL=jdbc:mysql://localhost:3306/db_bbsDB_USERNAME=rootDB_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;}// 关闭ResultSetpublic static void closeResultSet(ResultSet rs) {if (rs != null) {try {rs.close();rs = null;} catch (SQLException e) {e.printStackTrace();}}}// 关闭Statementpublic static void closeStatement(Statement stm) {if (stm != null) {try {stm.close();stm = null;} catch (SQLException e) {e.printStackTrace();}}}// 关闭PreparedStatementpublic static void closePreparedStatement(PreparedStatement pstm) {if (pstm != null) {try {pstm.close();pstm = null;} catch (SQLException e) {e.printStackTrace();}}}// 关闭Connectionpublic 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
9、你可能感兴趣的
【Java编程】写入、读取、遍历Properties文件 转载请注明出处：/andie_guo/article/details/25737031,谢谢！如果觉得《【Java编程】建立一个简单的JDBC连接-Drivers Connection Statement and PreparedStatement》对你有帮助，请点赞、收藏，并留下你的观点哦！
