JDBC:
java数据库连接;
java用来处理数据,连接到数据库,将数据发送到数据库执行保存;
JDBC是java连接数据库的一种规范:
是一种用于执行SQL语句的Java API,可以为多种关系型数据库提供统一访问,
它由一组用Java语言编写的类和接口组成。
Mysql-connector-java-8.0.16-->mysql数据库开发商,提供对java程序连接数据库的实现;
sqlServer 连接sqlserver的实现jar
Connection 接口 mysql connectionImpl implments Connection 实现 implments 实现
JDBC就是java与数据库的连接规范,具体由不同的开发商实现
JDBC搭建:
1.导入mysql开发商提供的连接mysql数据库的驱动包;
2.加载mysql驱动类;
3.建立与数据库的连接通道;
4.向数据库发送sql;
5.关闭与数据库连接通道;
public class Demo01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//根据类名加载mysql驱动类/反射实现
Class.forName("com.mysql.cj.jdbc.Driver");
//DriverManager.registerDriver(new Driver());
//建立与数据库的连接 并返回连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai","root","123");
System.out.println(connection);
String name = "赵柳";
String gender = "男";
String birthday = "2001-1-2";
String phone = "12300001111";
//获得的Statement,用来发送sql
Statement st = connection.createStatement();
st.executeUpdate("INSERT INTO student(NAME,gender,birthday,phone)" +
" VALUE('"+name+"','"+gender+"','"+birthday+"','"+phone+"')");
//关闭与数据库连接通道
st.close();
connection.close();
}
}
代码示意:
数据库通信信道:
//根据类名加载mysql驱动类/反射实现
Class.forName("com.mysql.cj.jdbc.Driver");
//DriverManager.registerDriver(new Driver());
//建立与数据库的连接 并返回连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai","root","123");
System.out.println(connection);
获得Statement执行sql语句
Statement st = connection.createStatement();
//用于执行ddl语句和增,删,改语句,返回操作的行数;
Int executeUpdate(String sql)
//用于执行查询语句,返回一个ResultSet集合
ResultSet executeQuery(String sql);
//修改
st.executeUpdate("update student set name='"+name+"',gender ='"+gender+"',birthday = '"+birthday+"'"+
"where no = "+no);
//删除
st.executeUpdate("delete from student where no="+no);
获得PrepareStatement执行sql语句
PrepareStatement ps = connection.prepareStatement(sql);
PrepareStatement中的方法:
Int executeUpdate() 用于执行ddl语句和dml(增,删,改)语句 返回操作的行数
用于执行查询语句 返回一个ResultSet 集合
ResultSet executeQuery();
public class Demo03 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1/schooldb?serverTimezone=Asia/Shanghai","root","123");
String name = "李1";
String gender = "男";
String birthday = "2001-1-2";
String phone = "12300001111";
String no = "14";
//预编译方式 预先将sql及参数预编译到PreparedStatement对象中
PreparedStatement ps = connection.prepareStatement("insert into student(name,gender,birthday,phone)" +
"value(?,?,?,?)");
ps.setObject(1,name);
ps.setObject(2,gender);
ps.setObject(3,birthday);
ps.setObject(4,phone);
ps.executeUpdate();//执行
//?表示参数占位
PreparedStatement ps = connection.prepareStatement("update student set name=?,gender=?,birthday=?" +
"where no = ? ");
ps.setObject(1,name);
ps.setObject(2,gender);
ps.setObject(3,birthday);
ps.setObject(4,no);
ps.executeUpdate();
PreparedStatement ps = connection.prepareStatement("delete from student where no=?");
ps.setObject(1,no);
ps.executeUpdate();
ps.close();
connection.close();
}
}
关闭与数据库的链接通道
每次操作完成后关闭所有与数据库交互的通道
st.close();
rs.close();
conn.close();
ps.close();
PreparedStatement和Statement:
1.代码的可读性和可维护性。
2.最重要的一点是极大地提高了安全性,防止sql注入;
结果集处理:
1.PreparedStatement和Statement中的excute Query()方法中会返回一个ResultSet对象,查询的结果就封装在此对象中;
2.使用ResultSet中的next()方法获得下一行数据
3.使用getXXX(String name)方法获得值
public class Demo05 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai","root","123");
String no = "16";
PreparedStatement ps = connection.prepareStatement(" SELECT NO,NAME,gender,birthday,height,phone,address,reg_time FROM student WHERE NO = ?\n");
ps.setObject(1,no);
//执行查询语句 将查询到的数据封装在ResultSet对象中,
//再从ResultSet中将数据提取出来,封装到自定义的类中
ResultSet rs = ps.executeQuery();
//rs.next() 判断有没有下一条数据,将光标移到下一行
Student student = new Student();
while (rs.next()) {
student.setNo(rs.getInt("no"));
student.setName(rs.getString("NAME"));
student.setGender(rs.getString("gender"));
student.setHeight(rs.getFloat("height"));
student.setPhone(rs.getString("phone"));
student.setAddress(rs.getString("address"));
student.getBirthday(rs.getDate("birthday"));
student.getRed_time(rs.getTimestamp("reg_time"));
}
System.out.println(student);
}
}