一、JDBC的步骤：以连接MySQL数据库为例
1、加载驱动
Class.forName("com.mysql.jdbc.Driver");2、创建连接
Connection c= DriverManager.getConnection(
"jdbc:mysql://localhost:3306/0403oop-jdbc","root","root");3、编写sql语句
//增加语句
String str="insert into student values(1,'赵六','男',19);"
//删除语句
String str="delete from student where id=1";
//修改语句
String str="update student set name='张三丰' where id=1;"
4、预处理SQL语句(preparedStatement)
PreparedStatement ps=c.prepareStatement(str);5、执行sql语句并接受返回的结果
int i= ps.executeUpdate();6、处理结果
System.out.println("结果："+i);
7、关闭连接
ps.close();
c.close();完整代码如下：
public class Test {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2、创建连接
Connection c= DriverManager.getConnection(
"jdbc:mysql://localhost:3306/0403oop-jdbc","root","root");
//3、编写sql语句
String str="delete from student where id=5";
//4、创建PreparedStatement对象进行预处理
PreparedStatement ps=c.prepareStatement(str);
//5、执行sql语句并接受返回的结果
int i= ps.executeUpdate();
//6、处理结果
System.out.println("结果："+i);
//7、关闭连接
ps.close();
c.close();
}
}二、为方便使用，可以把JDBC的某些步骤封装成方法来调用，减少代码量，提高代码简洁性。
1、建立一个类（对创建连接和关闭资源的BaseDao工具类的封装）
（1）加载驱动
（2）创建连接
//打开连接的方法
public void openCon(){
try {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2、创建连接
Connection con= DriverManager.getConnection(
"jdbc:mysql://localhost:3306/0403oop-jdbc",
"root","root");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}（3）编写SQL语句
（4）预处理SQL语句
（5）执行并返回结果
（6）处理结果
（7）关闭资源
//关闭资源的方法
public void closeAll() {
try {
//7、关闭资源
if (rs != null) {//防止空指针异常
rs.close();
}
if (pst!=null) {
pst.close();
}
if (con !=null) {
con.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
完整代码如下：
import java.sql.*;
public class BaseDao {
protected Connection con;
protected PreparedStatement pst;
protected ResultSet rs;
//打开连接的方法
public void openCon(){
try {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2、创建连接
con= DriverManager.getConnection(
"jdbc:mysql://localhost:3306/0403oop-jdbc",
"root","root");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
//3编写SQL语句
//4、预处理SQL语句
//5、执行并返回结果
//6、处理结果
//关闭资源的方法
public void closeAll() {
try {
//7、关闭资源
if (rs != null) {//防止空指针异常
rs.close();
}
if (pst!=null) {
pst.close();
}
if (con !=null) {
con.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}2、以MySQL的查询（select）语句为例
(1)对实体类Student进行封装
public class Student {
private int id;
private String name;
private String sex;
private int age;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Student() {
}
public Student(int id, String name, String sex, int age) {
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
}
}(2)创建一个Method方法类继承BaseDao
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class Methods extends BaseDao{//extends继承BaseDao
//查询所有数据的方法
public List<Student> selectAll(){
List<Student> list=null;
openCon();
String sql="select * from student";
try {
pst=con.prepareStatement(sql);
rs=pst.executeQuery();
list=new ArrayList<Student>();
while (rs.next()){
Student stu=new Student();
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setSex(rs.getString("sex"));
stu.setAge(rs.getInt("age"));
list.add(stu);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {//无论如何都要执行
closeAll();
}
return list;
}
//通过主键ID进行查询
public List<Student> selectById(int i){
List<Student> list=null;
openCon();
String sql="select * from student where id=?";
try {
pst=con.prepareStatement(sql);
pst.setInt(1,i);
rs=pst.executeQuery();
list=new ArrayList<Student>();
while (rs.next()){
Student stu=new Student();
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setSex(rs.getString("sex"));
stu.setAge(rs.getInt("age"));
list.add(stu);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {//无论如何都要执行
closeAll();
}
return list;
}
//通过主键ID进行查询,返回对象类型
public Student selectById01(int i){
Student stu=null;
openCon();
String sql="select * from student where id=?";
try {
pst=con.prepareStatement(sql);
pst.setInt(1,i);
rs=pst.executeQuery();
while (rs.next()){
stu=new Student();
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setSex(rs.getString("sex"));
stu.setAge(rs.getInt("age"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {//无论如何都要执行
closeAll();
}
return stu;
}
//模糊查询
public List<Student> selectByLike(String a,String b){
List<Student> list=null;
openCon();
String sql="select * from student where name like ? or sex like ?";
try {
pst=con.prepareStatement(sql);
pst.setString(1,"%"+a+"%");
pst.setString(2,"%"+b+"%");
rs=pst.executeQuery();
list=new ArrayList<Student>();
while (rs.next()){
Student stu=new Student();
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setSex(rs.getString("sex"));
stu.setAge(rs.getInt("age"));
list.add(stu);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {//无论如何都要执行
closeAll();
}
return list;
}
//分页查询
public List<Student> selectByLimit(int a,int b){
List<Student> list=null;
openCon();
String sql="select * from student limit ? offset ?";
try {
pst=con.prepareStatement(sql);
pst.setInt(1,a);
pst.setInt(2,b);
rs=pst.executeQuery();
list=new ArrayList<Student>();
while (rs.next()){
Student stu=new Student();
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setSex(rs.getString("sex"));
stu.setAge(rs.getInt("age"));
list.add(stu);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {//无论如何都要执行
closeAll();
}
return list;
}
}
(3)Test测试
import java.util.List;
public class Test {
public static void main(String[] args) {
Methods m=new Methods();
System.out.println("-----------查询全部数据--------");
List<Student> list=m.selectAll();
for (Student stu:list) {
System.out.println(stu.getId()+"\t"
+stu.getName()+"\t"
+stu.getSex()+"\t"
+stu.getAge());
}
System.out.println("--------------通过id查询，返回集合----------------");
list=m.selectById(3);
for (Student stu:list) {
System.out.println(stu.getId()+"\t"
+stu.getName()+"\t"
+stu.getSex()+"\t"
+stu.getAge());
}
System.out.println("----------通过id查询，返回对象--------------");
Student stu=m.selectById01(3);
System.out.println(stu.getId()+"\t"
+stu.getName()+"\t"
+stu.getSex()+"\t"
+stu.getAge());
System.out.println("----------模糊查询--------------");
list= m.selectByLike("王","男");
for (Student student:list) {
System.out.println(student.getId()+"\t"
+student.getName()+"\t"
+student.getSex()+"\t"
+student.getAge());
}
System.out.println("-----------分页查询------------");
list= m.selectByLimit(1,3);
for (Student student:list) {
System.out.println(student.getId()+"\t"
+student.getName()+"\t"
+student.getSex()+"\t"
+student.getAge());
}
}
}
1350
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
