本文详细介绍了JDBC的基本概念和使用步骤，包括注册驱动、创建连接、执行SQL语句等，并通过具体实例展示了如何使用Statement和PreparedStatement进行数据的增删改查操作，同时对比了两者的优劣。
JDBC即java数据库连接
JDBC的使用步骤：
（1）注册驱动（实际的作用是检查是否导入jar包）
（2）创建连接（利用DriverManager的getConnection（url,username,password）方法获取）
（3）准备要执行的SQL语句
（4）利用connection对象获取Statement对象（利用connection对象的createStatement的方法获取，如果获取PreparedStatement对象则利用connection对象的preparedStatement的方法获取）
（5）执行SQL语句（此时若是执行的是insert、delete、update的操作时是没有结果集的，若执行的是select会返回一个结果集ResultSet）
（6）关闭资源
举个栗子：我们在MySQL中创建一个dept的部门表，其中包含deptno（部门编号）、dname(部门名称)、location_id（位置）
表格的最终结构为：
创建表格的代码如下：
create table dept(deptno int primary key auto_increment,
dname varchar(20) not null unique,
location_id int);
目前在MySQL中的操作就完成了，此时我们把视角切换到eclipse中。
在eclipse中首先创建一个项目，然后创建一个lib的目录，用来存放数据库连接驱动jar包mysql-connector-java-5.1.30.jar，导入之后不要忘记了build path。
创建Dept实体类：
package an.sz.test;
public class Dept {
private int deptno;
private String dname;
private int locationId;
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public int getLocationId() {
return locationId;
}
public void setLocationId(int locationId) {
this.locationId = locationId;
}
@Override
public String toString() {
return "Dept [deptno=" + deptno + ", dname=" + dname + ", locationId=" + locationId + "]";
}
}
为了提高代码的封装性和复用性我们抽取了相同的代码为工具类：
package an.sz.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
/**
* 工具类
*/
public class JdbcUtil {
public static String driver;
private static String url;
private static String username;
private static String password;
static {
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
driver = bundle.getString("driver");
url = bundle.getString("url");
username = bundle.getString("username");
password = bundle.getString("password");
try {
//注册驱动
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(url,username,password);
}catch(Exception e) {
e.printStackTrace();
}
return conn;
}
//关闭state
public static void closeState(Statement state) {
if(state!=null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//关闭conn
public static void closeConn(Connection conn) {
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void closeRes(ResultSet res) {
if(res!=null) {
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//关闭资源
public static void closeResource(Statement state,Connection conn,ResultSet res) {
closeState(state);
closeConn(conn);
closeRes(res);
}
}
测试JDBC的测试类：
在这里插入代码片package an.sz.test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import an.sz.util.JdbcUtil;
public class TestJDBC {
public static void main(String[] args) {
// insertData();
// updateData();
// selectData();
// selectPage(3,2);
selectSql("研发部' or 1=1 -- ",1);
}
//插入数据
public static void insertData() {
Connection conn = null;
Statement state = null;
try {
//创建连接
conn = JdbcUtil.getConnection();
//获取statement对象
state = conn.createStatement();
//执行语句
int num = state.executeUpdate("insert into dept values(default,'教育部',3)");
System.out.println(num);
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.closeResource(state, conn,null);
}
}
//更新数据
public static void updateData() {
Connection conn = null;
Statement state = null;
try {
//获取connection对象
conn = JdbcUtil.getConnection();
//创建sql语句
String sql = "update dept set dname = '研发部' ,location_id=1 where deptno=5";
//获取statement对象
state = conn.createStatement();
//执行SQL语句
int num = state.executeUpdate(sql);
System.out.println(num);
}catch(Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.closeResource(state, conn,null);
}
}
//根据id查询数据
public static void selectData() {
Connection conn = null;
Statement state = null;
ResultSet res = null;
try {
conn = JdbcUtil.getConnection();
state = conn.createStatement();
String sql = "select * from dept where deptno='4'";
res = state.executeQuery(sql);
while(res.next()) {
int deptno = res.getInt("deptno");
String dname = res.getString("dname");
int location_id = res.getInt("location_id");
System.out.println(deptno+"\t"+dname+"\t"+location_id);
}
}catch(Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.closeResource(state, conn,res);
}
}
//利用分页查询所有结果
public static void selectPage(int currentPage,int pageNum) {
Connection conn = null;
Statement state = null;
ResultSet res = null;
try {
//利用工具类创建connection对象
conn = JdbcUtil.getConnection();
state = conn.createStatement();
String sql = "select * from dept";
res = state.executeQuery(sql);
int begin = (currentPage-1)*pageNum;
int end = currentPage*pageNum;
int currentNum=0;
while(res.next()) {
if(currentNum>=begin && currentPage<end) {
System.out.println(res.getInt("deptno")+"-->"+res.getString("dname")+"-->"+res.getInt("location_id"));
if(currentNum==end-1) {
break;
}
}
currentNum++;
}
}catch(Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.closeResource(state, conn, res);
}
}
//sql注入
public static void selectSql(String dname,int location_id) {
Connection conn = null;
Statement state = null;
ResultSet res = null;
try {
conn = JdbcUtil.getConnection();
state = conn.createStatement();
String sql = "select * from dept where dname='"+dname+"' and location_id="+location_id;
res = state.executeQuery(sql);
while(res.next()) {
System.out.println(res.getInt("deptno")+"-->"+res.getString("dname")+"-->"+res.getInt("location_id"));
}
}catch(Exception e) {
e.printStackTrace();
}finally{
JdbcUtil.closeResource(state, conn, res);
}
}
}
至此JDBC的操作就完成啦。
preparedStatement和Statement的比较：
•PreparedStatement 接口继承 Statement 接口
•PreparedStatement 效率高于 Statement
•PreparedStatement 支持动态绑定参数
•PreparedStatement 具备 SQL 语句预编译能力
• 使用 PreparedStatement 可防止出现 SQL 注入问题
由此可以看出PreparedStatement较之于Statement更实用下面将讲述利用PreparedStatement的JDBC的操作。
package an.sz.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import an.sz.util.JdbcUtil;
public class TestPreparedStatement {
public static void main(String[] args) {
// insertPrepared("技术部",5);
// updatePrepared(1,"数学部",6);
/*Dept d = selectPrepared(1);
if(d != null) {
System.out.println(d);
}*/
/*List<Dept> list = selectManyPrepared("部");
for (Dept dept : list) {
System.out.println(dept);
}*/
/*List<Dept> list = new ArrayList<>();
for(int i=1;i<=10;i++) {
Dept d = new Dept();
d.setDname("人力资源部"+i);
d.setLocationId(10+i);
list.add(d);
}
insertManyPrepared(list);*/
deletePrepared("人力");
}
//用preparedStatement插入一条数据
public static void insertPrepared(String dname,int locationId) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement("insert into dept values(default,?,?)");
ps.setString(1, dname);
ps.setInt(2, locationId);
ps.execute();
}catch(Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.closeResource(ps, conn, null);
}
}
//用preparedStatement更新一条数据
public static void updatePrepared(int deptno,String dname,int locationId) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtil.getConnection();
String sql = "update dept set dname=?,location_id=? where deptno=?";
ps = conn.prepareStatement(sql);
ps.setString(1, dname);
ps.setInt(2, locationId);
ps.setInt(3, deptno);
ps.execute();
}catch(Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.closeResource(ps, conn, null);
}
}
//用preparedStatement查询单条数据(封装成dept对象)
public static Dept selectPrepared(int deptno) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet res = null;
Dept d = null;
try {
conn = JdbcUtil.getConnection();
String sql = "select * from dept where deptno = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, deptno);
res = ps.executeQuery();
while(res.next()) {
d = new Dept();
d.setDeptno(res.getInt("deptno"));
d.setDname(res.getString("dname"));
d.setLocationId(res.getInt("location_id"));
}
}catch(Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.closeResource(ps, conn, res);
}
return d;
}
//用preparedStatement查询多条数据
public static List<Dept> selectManyPrepared(String namelike){
Connection conn = null;
PreparedStatement ps = null;
ResultSet res = null;
List<Dept> list = new ArrayList<Dept>();
try {
conn = JdbcUtil.getConnection();
String sql = "select * from dept where dname like ?";
ps = conn.prepareStatement(sql);
ps.setString(1, "%"+namelike+"%");
res = ps.executeQuery();
while(res.next()) {
Dept d = new Dept();
d.setDeptno(res.getInt("deptno"));
d.setDname(res.getString("dname"));
d.setLocationId(res.getInt("location_id"));
list.add(d);
}
}catch(Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.closeResource(ps, conn, res);
}
return list;
}
//用preparedStatement批量处理插入多条数据
public static void insertManyPrepared(List<Dept> list) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtil.getConnection();
String sql = "insert into dept values(default,?,?)";
ps = conn.prepareStatement(sql);
for(int i=0;i<list.size();i++) {
ps.setString(1, list.get(i).getDname());
ps.setInt(2, list.get(i).getLocationId());
//添加批处理
ps.addBatch();
}
ps.executeBatch();
}catch(Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.closeResource(ps, conn, null);
}
}
//在jdbc中的事务提交
public static void deletePrepared(String nameLike) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtil.getConnection();
//设置事务自动提交关闭(默认情况下是开启自用提交的)
conn.setAutoCommit(false);
ps = conn.prepareStatement("delete from dept where dname like ?");
ps.setString(1, "%"+nameLike+"%");
ps.execute();
conn.commit();
}catch(Exception e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {
JdbcUtil.closeResource(ps, conn, null);
}
}
}
扫一扫
2774
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
