目录
1、JDBC(Java DataBase Connectivity)
2、java程序连接数据库(mysql sqlserver oracle)
一、JDBC概述
1、JDBC(Java DataBase Connectivity)
java数据库连接,是一种用于执行SQL语句的java API,可以为多种关系型数据库提供统一访问,它由一组用Java语言编写的类和接口组成。
Java API:供程序调用的接口类,集成在java.sql包中
DriverManager类作用:管理各种不同的jDBC驱动
Connection 接口 与特定数据库的连接
Statement 接口 执行sql
PreparedStatement接口 执行sql
ResultSet接口 接收查询结果
2、java程序连接数据库(mysql sqlserver oracle)
不同的数据库实现方式有所不同,那么java要连接不同的数据库,需要写不同实现细节(很麻烦,对于语言开发者来说不太友好)
怎么来解决java程序连接不同的数据库问题呢?
java语言开发者 制定出了一套标准的访问数据库接口
save update delete query
(程序对数据库的操作一般有新增,修改,(删除 dml),查询)
然后由不同的数据库开发商来具体实现
实现java提供的标准对数据库操作的接口中的方法 save update delete query
然后作为程序开发者,只需要学习标准接口的功能即可
java中提供Connection接口,mysql有一个实现类ConnectionImpl(implments)
mysql开发商将连接mysql具体的实现功能封装到mysql-connector-java-8.0016.jar文件中
我们连接mysql时,只需要将此包导入到项目中即可
二、JDBC搭建
public class demo1 {
/*
1. 导入mysql驱动包
2. 注册驱动
3. 建立与mysql的连接驱动
4. 向mysql发送sql语句
5. 接收返回效果
6. 关闭连接通道
*/
public static void main(String[] args) {
try {
// 初始化驱动程序,这样就可以打开与数据库的通信信道
Class.forName("com.mysql.cj.jdbc.Driver"); //以java反射机制来创建此类对象
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//characterEncoding=utf8编码设置,连接mysql8 必须设置时区Timezone=Asia/Shanghai
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
try {
//Connection接口 实际返回的mysql的实现类对象
Connection connection = DriverManager.getConnection(url,"root","root");
//Statement 对象 发送sql
Statement statement = connection.createStatement();
//statement.executeUpdate(); statement.executeQuery();
statement.executeUpdate("insert into major(mname)values('网络')");
//statement.executeQuery();
statement.close();
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
三、PreparedStatement和Statement
public class demo2 {
public static void main(String[] args) {
try {
//save();
delete();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
// 删除
public static void delete() throws ClassNotFoundException, SQLException {
String id = "8";
Connection connection = null;
Statement statement = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
connection = DriverManager.getConnection(url,"root","root");
statement = connection.createStatement();
statement.executeUpdate("delete from student where id = "+id);
}finally {
if (statement!=null){
statement.close();
}
if (connection!=null){
connection.close();
}
}
}
// 新增
public static void save() throws ClassNotFoundException, SQLException {
int num = 108;
String name = "tom";
String gender = "女";
String birthday = "2001-9-1";
float height = 1.95f;
Connection connection = null;
Statement statement = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
connection = DriverManager.getConnection(url,"root","root");
statement = connection.createStatement();
statement.executeUpdate("insert into student(num,sname,gender,birthday,height)"+
"values("+num+",'"+name+"','"+gender+"','"+birthday+"',"+height+")");
}finally {
if (statement!=null){
statement.close();
}
if (connection!=null){
connection.close();
}
}
}
}
public class demo3 {
public static void main(String[] args) {
try {
//save();
delete();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
// 新增
public static void save() throws ClassNotFoundException, SQLException {
int num = 108;
String name = "tom";
String gender = "女";
String birthday = "2001-9-1";
float height = 1.95f;
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
connection = DriverManager.getConnection(url,"root","root");
//使用PreparedStatement发送sql,建议使用的
//预编译sql 在此只是将sql编译到preparedStatement对象中,?称为占位符,表示此处需要传入一个值
preparedStatement = connection.prepareStatement("insert into student(num,sname,gender,birthday,height)"+
"values(?,?,?,?,?)");
// 向sql中的占位符赋值,赋值时可以对值进行检测
preparedStatement.setObject(1,num);
preparedStatement.setObject(2,name);
preparedStatement.setObject(3,gender);
preparedStatement.setObject(4,birthday);
preparedStatement.setObject(5,height);
preparedStatement.executeUpdate();//执行sql
}finally {
if (preparedStatement!=null){
preparedStatement.close();
}
if (connection!=null){
connection.close();
}
}
}
// 删除
public static void delete() throws ClassNotFoundException, SQLException {
String id = "9";
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
connection = DriverManager.getConnection(url,"root","root");
preparedStatement = connection.prepareStatement("delete from student where id =?");
preparedStatement.setObject(1,id);// 在赋值时,一个?对应一个值,不能传入其他的关键字,防止爱sql注入攻击,安全性好
preparedStatement.executeUpdate();
}finally {
if (preparedStatement!=null){
preparedStatement.close();
}
if (connection!=null){
connection.close();
}
}
}
}
PreparedStatement和Statement的区别:
Statement 传参是直接将变量拼接到字符中,书写比较复杂,不能防止sql注入
statement.executeUpdate("insert into student(num,sname,gender,birthday,height)"+ "values("+num+",'"+name+"','"+gender+"','"+birthday+"',"+height+")");
PreparedStatement输入方式优雅,可以防止sql注入
preparedStatement = connection.prepareStatement("insert into student(num,sname,gender,birthday,height)"+ "values(?,?,?,?,?)"); // 向sql中的占位符赋值,赋值时可以对值进行检测 preparedStatement.setObject(1,num); preparedStatement.setObject(2,name); preparedStatement.setObject(3,gender); preparedStatement.setObject(4,birthday); preparedStatement.setObject(5,height);
new Date(); //部署项目的服务器所在电脑的时间 now(); // 取得数据库所在的系统的时间
四、结果集处理
public class demo4 {
/*
1. 导入mysql驱动包
2. 注册驱动
3. 建立与mysql的连接驱动
4. 向mysql发送sql语句
5. 接收返回效果
6. 关闭连接通道
*/
public static void main(String[] args) {
try {
Student student = query1("102");
System.out.println(student);
List<Student> list = query2("男");
System.out.println(list);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
// 查询单条数据
public static Student query1(String num) throws ClassNotFoundException, SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
connection = DriverManager.getConnection(url,"root","root");
//查询语句是有返回结果的,如何接收结果
//通过学号查询,只返回一条记录,向sql中传入参数,并返回结果
preparedStatement=connection.prepareStatement("select id,num,sname,gender,birthday,height,register_time from student where num=?");
preparedStatement.setObject(1,num);
resultSet = preparedStatement.executeQuery();//执行查询语句,接收返回的结果,在Java中存储结果
//循环 ResultSet next()将光标移到下一行,如果有数据返回true
Student student = new Student();
while (resultSet.next()){
/*System.out.println(resultSet.getInt(1));
System.out.println(resultSet.getInt(2));*/
student.setId(resultSet.getInt("id"));
student.setNum(resultSet.getInt("num"));
student.setSname(resultSet.getString("sname"));
student.setGender(resultSet.getString("gender"));
student.setBirthday(resultSet.getDate("birthday"));
student.setHeight(resultSet.getFloat("height"));
student.setRegisterTime(resultSet.getTimestamp("register_time"));//datetime 年月日 时分秒 getTimestamp 获取
}
return student;
}finally {
if (preparedStatement!=null){
preparedStatement.close();
}
if (connection!=null){
connection.close();
}
}
}
//查询多条数据,用集合接收
public static List<Student> query2(String gender) throws ClassNotFoundException, SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
ArrayList<Student> arrayList = new ArrayList<>();//接收查询到的多条数据记录
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/schooldb?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
connection = DriverManager.getConnection(url,"root","root");
//查询语句是有返回结果的,如何接收结果
//通过学号查询,只返回一条记录,向sql中传入参数,并返回结果
preparedStatement=connection.prepareStatement("select id,num,sname,gender,birthday,height,register_time from student where gender=?");
preparedStatement.setObject(1,gender);
resultSet = preparedStatement.executeQuery();//执行查询语句,接收返回的结果,在Java中存储结果
//循环 ResultSet next()将光标移到下一行,如果有数据返回true
while (resultSet.next()){
//每循环一次,创建一个学生对象,将当前记录数据封装到一个学生对象中
Student student = new Student();
/*System.out.println(resultSet.getInt(1));
System.out.println(resultSet.getInt(2));*/
student.setId(resultSet.getInt("id"));
student.setNum(resultSet.getInt("num"));
student.setSname(resultSet.getString("sname"));
student.setGender(resultSet.getString("gender"));
student.setBirthday(resultSet.getDate("birthday"));
student.setHeight(resultSet.getFloat("height"));
student.setRegisterTime(resultSet.getTimestamp("register_time"));//datetime 年月日 时分秒 getTimestamp 获取
arrayList.add(student);
}
return arrayList;
}finally {
if (preparedStatement!=null){
preparedStatement.close();
}
if (connection!=null){
connection.close();
}
}
}
}
public class Student {
private int id;
private int num;
private String sname;
private String gender;
private Date birthday;
private float height;
private Date registerTime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public float getHeight() {
return height;
}
public void setHeight(float height) {
this.height = height;
}
public Date getRegisterTime() {
return registerTime;
}
public void setRegisterTime(Date registerTime) {
this.registerTime = registerTime;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", num=" + num +
", sname='" + sname + '\'' +
", gender='" + gender + '\'' +
", birthday=" + birthday +
", height=" + height +
", registerTime=" + registerTime +
'}';
}
}