目录
一. JDBC概述
jdbc:java数据库连接(java database connection)
java程序可以连接不同的数据库,但数据库连接的具体细节有所不同,所以java中定义了一系列与数据库有关的类和接口,不同的数据库公司来实现java中的接口,并将其打包为jar包,我们只需要将其导入到java程序中,再通过调用类中的方法来达到java连接数据库的目的
总结:
1.java连接数据库的细节是由数据库公司实现的,并将其打包成jar包
2.我们使用不同的数据库就要去不同的数据库官网找到对应的jar包,来进行连接
二. JDBC连接数据库的步骤
- 去官网下载对应的数据库的jar包,在IDEA中创建一个项目,并在项目下面创建一个文件夹,建议命名为lib,并将jar复制到lib文件夹中,右键lib找到add as library,点击后jar包里含有其他文件就表示导入成功
2. 随便创建一个类,在main方法中加载驱动类
Class.forName("com.mysql.cj.jdbc.Driver");//方式一:反射
DriverManager.registerDriver(new Driver()); //方式二
第一种是通过反射机制实现
第二种是调用DriverManager类中的静态方法registerDriver,传入一个Driver类的对象
两种随便选一个即可
3. 建立与数据库的连接,获取连接对象
//3.建立与数据库的连接,获得连接对象
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url,user,password);
调用DriverManager类中的getConnection静态方法,传入url,数据库名和数据库密码,即可获得连接
4. 发送sql
//4.发送sql
Statement st = connection.createStatement();
st.executeUpdate("insert into major(name)values('机械')");
通过调用Connection类中的createStatement()方法获得发送对象st,再调用Statement类中的executeUpdate将要发送的sql语句以字符串的形式发送即可,发送成功后可在数据库中看出变化
5. 关闭数据库连接
st.close();
connection.close();
三. 利用JDBC进行数据库的增删改查
1. 获得Statement执行SQL语句
Statement执行SQL语句主要是进行字符串的拼接
1.1 增加数据
public void Save(String name,String gender,String birthday,String phone,String address,int majorid) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
//获取连接对象
Connection connection = DriverManager.getConnection(url,user,password);
//发送sql
Statement st = connection.createStatement();
st.executeUpdate("insert into student(name,gender,birthday,phone,address,reg_time,majorid)" +
"values('"+name+"','"+gender+"','"+birthday+"','"+phone+"','"+address+"',now(),"+majorid+")");
}
1.2 修改数据
public void Update(int num,String name,String gender,String birthday,String phone,String address,int majorid) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url,user,password);
Statement st = connection.createStatement();
st.executeUpdate("update student set name='"+name+"',"+"gender='"+gender+"',"+"birthday='"+birthday+"',"+"phone='"+phone+"',"+"address='"+address+"',"+"majorid="+majorid+" where num="+num);
st.close();
connection.close();
}
1.3 删除数据
public void delete(int num) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url,user,password);
Statement st = connection.createStatement();
st.executeUpdate("delete from student where num="+num);
}
1.4 查询返回值为一条的数据
public Student findByNum(int num) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url,user,password);
Statement st = connection.createStatement();
ResultSet rs = st.executeQuery("select name,gender,birthday,phone,reg_time from student where num="+num);
Student student = null;
while(rs.next())
{
student = new Student();
student.setName(rs.getString("name"));
student.setGender(rs.getString("gender"));
student.setBirthday(rs.getDate("birthday"));
student.setPhone(rs.getString("phone"));
student.setRegTime(rs.getTimestamp("reg_time"));
}
return student;
}
1.5 查询返回值为多条的数据
public ArrayList<Student> findByGender(String gender) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url,user,password);
Statement st = connection.createStatement();
ResultSet rs = st.executeQuery("select name,gender,birthday,phone,reg_time from student where gender='"+gender+"'");
ArrayList<Student> students = new ArrayList<>();
while(rs.next())
{
Student student = new Student();
student.setName(rs.getString("name"));
student.setGender(rs.getString("gender"));
student.setBirthday(rs.getDate("birthday"));
student.setPhone(rs.getString("phone"));
student.setRegTime(rs.getTimestamp("reg_time"));
students.add(student);
}
return students;
}
2. 获得PrepareStatement执行SQL语句
PrepareStatement执行SQL语句主要是利用占位符?来匹配
2.1 增加数据
public void Save(String name,String gender,String birthday,String phone,String address,int majorid) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
//获取连接对象
Connection connection = DriverManager.getConnection(url,user,password);
//发送sql
PreparedStatement ps = connection.prepareStatement("insert into student(name,gender,birthday,phone,address,reg_time,majorid)values(?,?,?,?,?,now(),?)");
ps.setObject(1,name);
ps.setObject(2,gender);
ps.setObject(3,birthday);
ps.setObject(4,phone);
ps.setObject(5,address);
ps.setObject(6,majorid);
ps.executeUpdate();
ps.close();
connection.close();
}
2.2 修改数据
public void Update(int num,String name,String gender,String birthday,String phone,String address,int majorid) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url,user,password);
PreparedStatement ps = connection.prepareStatement("update student set name=?,gender=?,birthday=?,phone=?,address=?,majorid=? where num=?");
ps.setObject(1,name);
ps.setObject(2,gender);
ps.setObject(3,birthday);
ps.setObject(4,phone);
ps.setObject(5,address);
ps.setObject(6,majorid);
ps.setObject(7,num);
ps.executeUpdate();
ps.close();
connection.close();
}
2.3 删除数据
public void delete(int num) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url,user,password);
PreparedStatement ps = connection.prepareStatement("delete from student where num=?");
ps.setObject(1, num);
ps.executeUpdate();
ps.close();
connection.close();
}
2.4 查询返回值为一条的数据
public Student findStudentByNum(int num) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url,user,password);
PreparedStatement ps = connection.prepareStatement("select num,name,gender,birthday,phone,reg_time from student where num=?");
ps.setObject(1, num);
//查询操作
ResultSet rs = ps.executeQuery();
Student student = null;
while(rs.next())
{
student = new Student();
student.setNum(rs.getInt("num"));
student.setName(rs.getString("name"));
student.setGender(rs.getString("gender"));
student.setBirthday(rs.getDate("birthday"));
student.setPhone(rs.getString("phone"));
student.setRegTime(rs.getTimestamp("reg_time"));
}
rs.close();
ps.close();
connection.close();
return student;
}
2.5 查询返回值为多条的数据
public ArrayList<Student> findStudentByGender(String gender) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url,user,password);
PreparedStatement ps = connection.prepareStatement("select num,name,gender,birthday,phone,reg_time from student where gender=?");
ps.setObject(1, gender);
ResultSet rs = ps.executeQuery();
ArrayList<Student> students = new ArrayList<>();
while(rs.next())
{
Student student = new Student();
student.setNum(rs.getInt("num"));
student.setName(rs.getString("name"));
student.setGender(rs.getString("gender"));
student.setBirthday(rs.getDate("birthday"));
student.setPhone(rs.getString("phone"));
student.setRegTime(rs.getTimestamp("reg_time"));
students.add(student);
}
rs.close();
ps.close();
connection.close();
return students;
}
注意:修改和删除调用的是executeUpdate,查询调用的是executeQuery,并且查询时时要将结果封装到对象中
3. 两种方式的区别
Statement:通过Statement方式执行SQL语句,因为是通过将参数拼接到字符串中所以书写方式复杂,并且不安全,别人可以通过给字符串中拼接一些其他条件导致修改表数据甚至是清空表数据
PrepareStatement:这种方式是通过占位符和setObject()方法执行SQL语句,如果参数多余占位符则会报错,所以外界是不能修改的,比第一种更安全
总结:
Statement书写麻烦,不安全
PrepareStatement书写简单,安全