浅学JDBC 笔记记录
- 一、
- 1. JDBC的概念
- 2. JDBC快速入门
- 2.1 DriverManager
- 2.2 Connection
- 2.3 Statement
- 2.4 ResultSet
- 3. JDBC入门案例使用
- 3.1 查询所有学生信息
- 3.2 根据id查询学生信息&&新增学生信息&&修改学生信息&&删除学生信息
一、
1. JDBC的概念
2. JDBC快速入门
import java.sql.*;public class JDBCDemo01 {public static void main(String[] args) throws ClassNotFoundException, SQLException {//1. 导入jar包 此处使用maven依赖引入//2. 注册驱动// 此步骤可以跳过 源码中已经填充// Class.forName("com.mysql.jdbc.Driver");//3. 获取连接Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db8", "root", "root");//4. 获取执行者对象Statement stat = con.createStatement();//5. 执行sql语句，并且接受sql语句返回结果String sql="select * from account";ResultSet rs = stat.executeQuery(sql);//6.处理结果while (rs.next()){System.out.println(rs.getInt("id")+"\t"+rs.getString("name")+"\t"+rs.getString("money"));}//7.释放相关资源con.close();stat.close();rs.close();}
}上面使用的类进行详解
2.1 DriverManager
2.2 Connection
2.3 Statement
2.4 ResultSet
3. JDBC入门案例使用
数据准备
create database db1;
use db1;
#创建Student表
create table student(id int primary key auto_increment,name varchar(20),age int,birthday Date
);
#添加数据
insert into student values (null,'张三',23,'1999-09-23'),(null,'李四',24,'1998-08-10'),(null,'王五',25,'1996-06-06')
,(null,'赵六',26,'1994-10-20');实体类
package domain;import java.util.Date;public class Student {private Integer id;private String name;private Integer age;private Date birthday;public Student() {}public Student(Integer id, String name, Integer age, Date birthday) {this.id = id;this.name = name;this.age = age;this.birthday = birthday;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}@Overridepublic String toString() {return "domain.Student{" +"id=" + id +", name='" + name + '\'' +", age=" + age +", birthday=" + birthday +'}';}
}3.1 查询所有学生信息
dao:
public interface StudentDao {ArrayList<Student> findAll();}
daoImpl:
public class StudentDaoImpl implements StudentDao{//查询所有学生信息@Overridepublic ArrayList<Student> findAll() {ArrayList<Student> list=new ArrayList();Connection con =null;Statement statement =null;ResultSet resultSet =null;try {//1.注册驱动Class.forName("com.mysql.jdbc.Driver");//2.获取连接con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "root");//3.获取执行者对象statement = con.createStatement();//4.执行sql语句。并且接受返回的结果集String sql="select * from student";resultSet = statement.executeQuery(sql);//5.处理结果集while (resultSet.next()){Integer id=resultSet.getInt("id");String name=resultSet.getString("name");Integer age=resultSet.getInt("age");Date date=resultSet.getDate("birthday");//封装Student对象Student student=new Student(id,name,age,date);//将Student保存到集合对象list.add(student);}} catch (Exception e) {e.printStackTrace();}finally {if (con!=null){try {con.close();} catch (SQLException e) {e.printStackTrace();}}if (statement!=null){try {statement.close();} catch (SQLException e) {e.printStackTrace();}}if (resultSet!=null){try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}}return list;}
}
service:
public interface StudentService {//查询所有学生信息ArrayList<Student> findAll();}
serviceImpl:
public class StudentServiceImpl implements StudentService{private StudentDao dao=new StudentDaoImpl();@Overridepublic ArrayList<Student> findAll() {return dao.findAll();} }
controller:
package controller;import domain.Student;
import org.junit.Test;
import service.StudentService;
import service.StudentServiceImpl;import java.util.ArrayList;
public class StudentController {private StudentService service=new StudentServiceImpl();@Testpublic void findAll(){ArrayList<Student> list = service.findAll();for (Student student:list){System.out.println(student);}}
}成功：
3.2 根据id查询学生信息&&新增学生信息&&修改学生信息&&删除学生信息
dao:
package dao;import domain.Student;import java.util.ArrayList;/**Dao层接口*/public interface StudentDao {//查询所有学生信息ArrayList<Student> findAll();//条件查询，根据id获取学生信息Student findById(Integer id);//新增学生信息int insert(Student stu);//修改学生信息int update(Student stu);//删除学生信息int delete(Integer id);
}daoImpl:
package dao;import domain.Student;import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
public class StudentDaoImpl implements StudentDao{//查询所有学生信息@Overridepublic ArrayList<Student> findAll() {ArrayList<Student> list=new ArrayList();Connection con =null;Statement statement =null;ResultSet resultSet =null;try {//1.注册驱动Class.forName("com.mysql.jdbc.Driver");//2.获取连接con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1?useSSL=false", "root", "root");//3.获取执行者对象statement = con.createStatement();//4.执行sql语句。并且接受返回的结果集String sql="select * from student";resultSet = statement.executeQuery(sql);//5.处理结果集while (resultSet.next()){Integer id=resultSet.getInt("id");String name=resultSet.getString("name");Integer age=resultSet.getInt("age");Date date=resultSet.getDate("birthday");//封装Student对象Student student=new Student(id,name,age,date);//将Student保存到集合对象list.add(student);}} catch (Exception e) {e.printStackTrace();}finally {if (con!=null){try {con.close();} catch (SQLException e) {e.printStackTrace();}}if (statement!=null){try {statement.close();} catch (SQLException e) {e.printStackTrace();}}if (resultSet!=null){try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}}return list;}//条件查询，根据id获取学生信息@Overridepublic Student findById(Integer id) {Student student=new Student();Connection con =null;Statement statement =null;ResultSet resultSet =null;try {//1.注册驱动Class.forName("com.mysql.jdbc.Driver");//2.获取连接con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1?useSSL=false", "root", "root");//3.获取执行者对象statement = con.createStatement();//4.执行sql语句。并且接受返回的结果集String sql="select * from student where id='"+id+"'";resultSet = statement.executeQuery(sql);//5.处理结果集while (resultSet.next()){Integer sid=resultSet.getInt("id");String name=resultSet.getString("name");Integer age=resultSet.getInt("age");Date date=resultSet.getDate("birthday");//封装Student对象student=new Student(sid,name,age,date);}} catch (Exception e) {e.printStackTrace();}finally {if (con!=null){try {con.close();} catch (SQLException e) {e.printStackTrace();}}if (statement!=null){try {statement.close();} catch (SQLException e) {e.printStackTrace();}}if (resultSet!=null){try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}}return student;}//新增学生信息@Overridepublic int insert(Student stu) {Connection con =null;Statement statement =null;int result=0;try {//1.注册驱动Class.forName("com.mysql.jdbc.Driver");//2.获取连接con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1?useSSL=false", "root", "root");//3.获取执行者对象statement = con.createStatement();//4.执行sql语句。并且接受返回的结果集Date birthday = stu.getBirthday();SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");String date = sdf.format(birthday);String sql="insert into student values('"+stu.getId()+ "','"+stu.getName()+"','"+stu.getAge()+"','"+date+"')";result = statement.executeUpdate(sql);//5.处理结果集} catch (Exception e) {e.printStackTrace();}finally {if (con!=null){try {con.close();} catch (SQLException e) {e.printStackTrace();}}if (statement!=null){try {statement.close();} catch (SQLException e) {e.printStackTrace();}}}return result;}//修改学生信息@Overridepublic int update(Student stu) {Connection con =null;Statement statement =null;int result=0;try {//1.注册驱动Class.forName("com.mysql.jdbc.Driver");//2.获取连接con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1?useSSL=false", "root", "root");//3.获取执行者对象statement = con.createStatement();//4.执行sql语句。并且接受返回的结果集Date birthday = stu.getBirthday();SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");String date = sdf.format(birthday);String sql="update student set id='"+stu.getId()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+date+"' where id='"+stu.getId()+"'";result = statement.executeUpdate(sql);//5.处理结果集} catch (Exception e) {e.printStackTrace();}finally {if (con!=null){try {con.close();} catch (SQLException e) {e.printStackTrace();}}if (statement!=null){try {statement.close();} catch (SQLException e) {e.printStackTrace();}}}return result;}//删除学生信息@Overridepublic int delete(Integer id) {Connection con =null;Statement statement =null;int result=0;try {//1.注册驱动Class.forName("com.mysql.jdbc.Driver");//2.获取连接con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1?useSSL=false", "root", "root");//3.获取执行者对象statement = con.createStatement();//4.执行sql语句。并且接受返回的结果集String sql="delete from student where id='"+id+"'";result = statement.executeUpdate(sql);} catch (Exception e) {e.printStackTrace();}finally {if (con!=null){try {con.close();} catch (SQLException e) {e.printStackTrace();}}if (statement!=null){try {statement.close();} catch (SQLException e) {e.printStackTrace();}}}return result;}
}service:
package service;import domain.Student;import java.util.ArrayList;public interface StudentService {//查询所有学生信息ArrayList<Student> findAll();//条件查询，根据id获取学生信息Student findById(Integer id);//新增学生信息int insert(Student stu);//修改学生信息int update(Student stu);//删除学生信息int delete(Integer id);
}serviceImpl:
package service;import dao.StudentDao;
import dao.StudentDaoImpl;
import domain.Student;import java.util.ArrayList;public class StudentServiceImpl implements StudentService{private StudentDao dao=new StudentDaoImpl();@Overridepublic ArrayList<Student> findAll() {return dao.findAll();}@Overridepublic Student findById(Integer id) {return dao.findById(id);}@Overridepublic int insert(Student stu) {return dao.insert(stu);}@Overridepublic int update(Student stu) {return dao.update(stu);}@Overridepublic int delete(Integer id) {return dao.delete(id);}
}
controller:
package controller;import com.sun.xml.internal.bind.v2.model.core.ID;
import domain.Student;
import org.junit.Test;
import service.StudentService;
import service.StudentServiceImpl;import java.util.ArrayList;
import java.util.Date;public class StudentController {private StudentService service=new StudentServiceImpl();//查询所有学生信息@Testpublic void findAll(){ArrayList<Student> list = service.findAll();for (Student student:list){System.out.println(student);}}//条件查询，根据id获取学生信息@Testpublic void findById(){Student student=service.findById(1);System.out.println(student);}//新增学生信息@Testpublic void insert(){Student student=new Student(5,"李薇",88,new Date());int result = service.insert(student);if (result!=0){System.out.println("添加成功");}else {System.out.println("添加失败");}}//修改学生信息@Testpublic void update(){Student stu = service.findById(5);stu.setName("李兔兔");int result = service.update(stu);if (result!=0){System.out.println("更新成功");}else {System.out.println("更行失败");}}//删除学生信息@Testpublic void delete(){int result = service.delete(5);if (result!=0){System.out.println("删除成功");}else {System.out.println("删除失败");}}
}
