【Java编程】建立一个简单的JDBC连接-Drivers, Connection, Statement and PreparedStatement-白红宇

【Java编程】建立一个简单的JDBC连接-Drivers, Connection, Statement and PreparedStatement

阅读量：6696 次

发布时间：2019-06-25

本文共 7222 字，大约阅读时间需要 24 分钟。

本blog提供了一个简单的通过JDBC驱动建立JDBC连接例程，并分别通过Statement和PreparedStatement实现对数据库的查询。

在下一篇blog中将重点比較Statement与PreparedStatement的差异。

1、为项目加入JDBC驱动

1）JDBC驱动下载

官方下载地址：

CSDN资料下载地址：

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 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;	}	// 关闭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
       
         users = new ArrayList
        
         ();		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
       
         users = new ArrayList
        
         ();		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);	}	}