- 本章学习目标
- JDBC简介
- JDBC原理
- 连接数据库
- 加载JDBC驱动程序
- 创建数据库连接
- Connection接口
- 创建Statement
- 执行SQL语句
- 处理返回结果
- 关闭创建的对象
- 操作数据库
- Statement -1
- Statement -2
- ResultSet -1
- 结果集
- ResultSet -2
- ResultSet -3
- PreparedStatement
- CallableStatement-1
- CallableStatement-2
- 事务操作
- 分页策略
- DatabaseMetaData
- ResultSetMetaData
- 小结-1
- 小结-2
- Statement使用实例
- PreparedStatement使用实例
- 封装JDBC的初始化和关闭
- Dao接口
- Dao的实现
- 使用
本章学习目标
学习了7个接口
- Connection接口
- Statemen接口
- PreparedStatement接口
- ResultSet接口
- CallableStatement接口
- DatabaseMetaData接口
- ResultSetMetaData接口
1个类 - DriverManager类
掌握JDBC的事务处理
理解分页策略
理解JDBC访问数据库的结构及原理
了解JDBC四种驱动类型
掌握JDBC访问数据库的步骤
掌握JDBC中的DriverManager类和Connection、ResultSet接口
掌握JDBC的常用查询接口
掌握JDBC对集元数据的访问
JDBC简介
Java数据库连接(Java Database Connectivity,JDBC),是一种用于执行SQL语句的Java API,它由一组用Java编程语言编写的类和接口组成。
JDBC为数据库开发人员提供了一个标准的API,使他们能够用纯Java API来编写数据库应用程序。
使用JDBC编写的程序能够自动地将SQL语句传送给相应的数据库管理系统。
JDBC扩展了Java的功能,由于Java语言本身的特点,使得JDBC具有简单、健壮、安全、可移植、获取方便等优势 。
JDBC原理
连接数据库
使用JDBC访问数据库的基本步骤一般如下:
- 加载JDBC驱动程序
- 建立数据库连接
- 创建Statement对象
- 执行SQL语句
- 处理返回结果
- 关闭结果集对象
- 关闭创建的Statement对象
- 关闭数据库连接
加载JDBC驱动程序
创建数据库连接
Connection接口
创建Statement
执行SQL语句
处理返回结果
关闭创建的对象
操作数据库
Statement -1
Statement -2
ResultSet -1
结果集
ResultSet -2
ResultSet -3
PreparedStatement
CallableStatement-1
继承PreparedStatement可以使用占位符
用来调用存储过程的函数
传值语句规范:{<procedure_name(参数)>}
例子:
public static void main(String[] args) {
Connection con = DBUtil.getConnection();
CallableStatement cst =null;
try {
cst = con.prepareCall("{Call getNameByEmpNo(?,?)}");
cst.setInt(1,100);
//注册存储过程参数模式为out的数据类型,getNameByEmpNo第二个参数?是out模式,参数类型是字符串
cst.registerOutParameter(2, Types.VARCHAR);
//执行存储过程
cst.execute();
//获取存储过程out参数传递回来的数值
System.out.println(cst.getString(2));
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.Close(con,cst,null);
}
}
注意点:若是参数是out类型,则需要对其进行注册才能够将其的返回值取出来。
CallableStatement-2
实例
public static void main(String[] args) {
Connection con = DBUtil.getConnection();
CallableStatement cst =null;
try {
cst = con.prepareCall("{Call getNameByEmpNo(?,?)}");
cst.setInt(1,100);
//注册存储过程参数模式为out的数据类型,getNameByEmpNo第二个参数?是out模式,参数类型是字符串
cst.registerOutParameter(2, Types.VARCHAR);
//执行存储过程
cst.execute();
//获取存储过程out参数传递回来的数值
System.out.println(cst.getString(2));
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.Close(con,cst,null);
}
}
注意点:若是参数是out类型,则需要对其进行注册才能够将其的返回值取出来。
注册函数:registerOutParameter(2, Types.VARCHAR);
事务操作
分页策略
DatabaseMetaData
用于得到关于数据库的信息
通过数据库连接对象获取对象
DatabaseMetaData metaData = con.getMetaData();
实例
public static void main(String[] args) {
Connection con = DBUtil.getConnection();
ResultSet rs = null;
try {
DatabaseMetaData metaData = con.getMetaData();
System.out.println("数据库名称:" + metaData.getDatabaseProductName());
System.out.println("数据库版本:" + metaData.getDatabaseProductVersion());
System.out.println("数据库主版本号:" + metaData.getDatabaseMajorVersion());
System.out.println("数据库次版本号:" + metaData.getDatabaseMinorVersion());
System.out.println("驱动名称:" + metaData.getDriverName());
System.out.println("驱动版本:" + metaData.getDriverVersion());
System.out.println("驱动主版本:" + metaData.getDriverMajorVersion());
System.out.println("驱动次版本:" + metaData.getDriverMinorVersion());
//获取数据库所有的模式对象(模式指用户)
ResultSet schemas = metaData.getSchemas();
while (schemas.next()) {
System.out.println(schemas.getString(1));
}
System.out.println("查询XE数据库下的所有模式(所有用户)---------------------------------------------------");
rs = metaData.getSchemas();
while (rs.next()) {
System.out.println(rs.getString("TABLE_SCHEM"));
}
System.out.println("查询jsd2101模式下的所有表---------------------------------------------------");
//查询jsd2101模式下的所有表 注意模式应该大写
rs = metaData.getTables(null, "JSD2101", null, new String[]{"TABLE"});
while (rs.next()) {
System.out.println(rs.getString("TABLE_NAME"));
}
System.out.println("查询jsd2101模式下的所有(表,视图,同义词)---------------------------------------------------");
//查询jsd2101模式下的所有表 注意模式应该大写
rs = metaData.getTables(null, "JSD2101", null, null);
while (rs.next()) {
//可以打印出表和视图
System.out.println(rs.getString("TABLE_NAME") + "---------" + rs.getString("TABLE_TYPE"));
}
System.out.println("查询支持的对象类型(表,视图,同义词)---------------------------------------------------");
rs = metaData.getTableTypes();
while (rs.next()) {
System.out.println("支持的表类型" + rs.getString(1));
}
System.out.println("查询JSD2101用户下的STUDENT表的所有字段(null为所有)---------------------------------------------------");
//打印不出来注释去DBUtil包修改getConnection的方法采用getConnection(url,properties)的方法就解决注释不出来的问题
rs = metaData.getColumns(null, "JSD2101", "STUDENT", null);
//获取结果集的元数据
ResultSetMetaData metaData1 = rs.getMetaData();
System.out.println("总列数:"+metaData1.getColumnCount());
for (int i =1;i<=18;i++){
System.out.println(metaData1.getColumnName(i)+"---------"+metaData1.getColumnType(i)+"---------"+metaData1.getColumnTypeName(i));
}
while (rs.next()) {
System.out.println("列名称:" + rs.getString("COLUMN_NAME") + "---------" + " 列的类型:"
+ rs.getString("TYPE_NAME") + "------" + " 列的长度:" + rs.getInt("COLUMN_SIZE")
+ "---------" + "列表的注释:" + rs.getString("REMARKS"));
}
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
ResultSetMetaData
主要用来获取结果集的结构
ResultSetMetaData metaData1 = rs.getMetaData();
System.out.println("总列数:"+metaData1.getColumnCount());
for (int i =1;i<=18;i++){
System.out.println(metaData1.getColumnName(i)+"---------"+metaData1.getColumnType(i)+"---------"+metaData1.getColumnTypeName(i));
}
小结-1
小结-2
Statement使用实例
select的使用
public static void main(String[] args) {
try {
//1、加载驱动类
Class.forName("oracle.jdbc.driver.OracleDriver");
//2、获取数据库连接对象 访问谁的数据库就写谁的域名或则ip第地址
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "jsd2101", "jsd2101");
//3、创建数据库操作对象
Statement stm = con.createStatement();
//4、执行sql语句返回结果集
ResultSet rs = stm.executeQuery("select * from student s where s.id<500");
//5、结果集处理
while (rs.next()){//判断结果集中是否还有数据,有返回true否则返回false (类似迭代器的Next)
System.out.println("编号:"+rs.getInt("id"));
System.out.println("名字:"+rs.getString("name"));
System.out.println("生日:"+rs.getDate("birthday"));
System.out.println("创建时间:"+rs.getTimestamp("create_time"));
}
//6、关闭结果集
rs.close();
//7、关闭sql操作对象
stm.close();
//8、关闭数据库连接对象
con.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
update 和deleted 和insert的使用
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "jsd2101", "jsd2101");
Statement stm = con.createStatement();
//执行sql更新操作,返回更新的行数。
// int i = stm.executeUpdate("delete student where id <210");
// System.out.println("删除"+i+"条记录");
// int i = stm.executeUpdate("update student set password ='10086' where id <220");
// System.out.println("更新"+i+"条记录");
int i = stm.executeUpdate("insert into student(id,name,username,password,sex,age,birthday,create_time)" +
" values(2,'张三2','150111','12346789',1,21,to_date('2001-03-05','yyyy-mm-dd'),sysdate)");//返回操作了多少行
System.out.println("插入"+i+"条记录");
con.close();
stm.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
PreparedStatement使用实例
与 Statement一样,只不过多了占位符
update
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "jsd2101", "jsd2101");
//prepareStatement接口是Statement接口的子接口,支持占位符 '?'
PreparedStatement pst = con.prepareStatement("update student set password='104655000' where id<?");
//给占位符赋值,根据占位符赋值的类型不同选择不同的占位符setXXX方法,第一个占位符序号为1,以此推类
pst.setInt(1,10);
int i = pst.executeUpdate();//返回操作了多少行
System.out.println("更新了"+i+"行");
con.close();
pst.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
insert
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "jsd2101", "jsd2101");
PreparedStatement pst =con.prepareStatement("insert into student(id,name,username,password,sex,age,birthday,create_time) values (?,?,?,?,?,?,?,?)");
pst.setInt(1,4);
pst.setString(2,"清");
pst.setString(3,"124445");
pst.setString(4,"0000001");
pst.setInt(5,0);
pst.setInt(6,21);
pst.setDate(7,new Date(new java.util.Date().getTime()));
pst.setTimestamp(8,new Timestamp(System.currentTimeMillis()));
int i = pst.executeUpdate();
System.out.println("插入了"+i+"行");
con.close();
pst.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
select
public static void main(String[] args) {
Connection con = DBUtil.getConnection();
PreparedStatement pst = null;
ResultSet rs = null;
try {
pst = con.prepareStatement("select * from student where id<?");
pst.setInt(1, 10);
rs = pst.executeQuery();
while (rs.next()){//判断结果集中是否还有数据,有返回true否则返回false (类似迭代器的Next)
System.out.println("编号:"+rs.getInt("id"));
System.out.println("名字:"+rs.getString("name"));
System.out.println("生日:"+rs.getDate("birthday"));
System.out.println("创建时间:"+rs.getTimestamp("create_time"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.Close(con, pst, rs);
}
}
封装JDBC的初始化和关闭
编写配置文件
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:XE
username=jsd2101
password=jsd2101
编写工具类
public class DBUtil {
private static String driver;
private static String url;
private static String username;
private static String password;
static {
Properties p = new Properties();
try {
p.load(DBUtil.class.getResourceAsStream("/db.properties"));
driver = p.getProperty("driver");
url = p.getProperty("url");
username = p.getProperty("username");
password = p.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
Class.forName(driver);
//采用getConnection(url,properties)的方法就解决注释不出来的问题
Properties p = new Properties();
p.put("user",username);
p.put("password",password);
p.put("remarksReporting","true");
Connection con = DriverManager.getConnection(url,p);
return con;
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return null;
}
public static void Close(Connection con, Statement stm, ResultSet rs) {
if (rs != null) {
try {
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stm != null) {
try {
stm.close();
stm = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
con = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Dao接口
/**
* dao : data access object(数据访问对象 用来访问数据库)
*/
import entity.Student;
import java.util.List;
public interface StudentDao {
//增加用户
public void add(Student student);
//根据id删除用户
public void deleteById(int id);
//更新
public void update(Student student);
//更加id查找一个学生
public Student queryById(int id);
//分页
public List<Student> queryByIdPage(int pageNo,int pageSize);
//批量增加
public void addMore(List<Student>);
}
Dao的实现
public class StudentDaoImpl implements StudentDao {
@Override
public void add(Student student) {
Connection con = null;
PreparedStatement pst = null;
try {
con = DBUtil.getConnection();
pst = con.prepareStatement("insert into student(id,name,username,password,sex,age,birthday,create_time) values (studnet_0303.nextval,?,?,?,?,?,?,sysdate)");
pst.setString(1, student.getName());
pst.setString(2, student.getUsername());
pst.setString(3, student.getPassword());
pst.setInt(4, student.getSex());
pst.setInt(5, student.getAge());
pst.setDate(6, new Date(student.getBirthday().getTime()));
int i = pst.executeUpdate();
System.out.println("插入了" + i + "行");
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.Close(con, pst, null);
}
}
@Override
public void deleteById(int id) {
Connection con = null;
PreparedStatement pst = null;
try {
con = DBUtil.getConnection();
pst = con.prepareStatement("delete student where id = ?");
pst.setInt(1, id);
int i = pst.executeUpdate();
System.out.println("删除了条" + i + "数据");
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.Close(con, pst, null);
}
}
@Override
public void update(Student student) {
Connection con = null;
PreparedStatement pst = null;
try {
con = DBUtil.getConnection();
pst = con.prepareStatement("update student set name=?,username=?,password=?,sex=?,age=?,birthday=?,create_time=sysdate where id =?");
pst.setString(1, student.getName());
pst.setString(2, student.getUsername());
pst.setString(3, student.getPassword());
pst.setInt(4, student.getSex());
pst.setInt(5, student.getAge());
pst.setDate(6, new Date(student.getBirthday().getTime()));
pst.setInt(7, student.getId());
int i = pst.executeUpdate();
System.out.println("更新了" + i + "条数据");
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.Close(con, pst, null);
}
}
@Override
public Student queryById(int id) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
Student student = new Student();
try {
con = DBUtil.getConnection();
pst = con.prepareStatement("select * from student where id =?");
pst.setInt(1, id);
rs = pst.executeQuery();
while (rs.next()) {
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setUsername(rs.getString("username"));
student.setPassword(rs.getString("password"));
student.setSex(rs.getInt("sex"));
student.setAge(rs.getInt("age"));
student.setBirthday(rs.getDate("birthday"));
student.setCreateTime(rs.getTimestamp("create_time"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.Close(con, pst, rs);
}
return student;
}
@Override
public List<Student> queryByIdPage(int pageNo, int pageSize) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
List<Student>list=new ArrayList<>();
Student student = null;
try {
con = DBUtil.getConnection();
pst = con.prepareStatement("select ss.*,rownum from (select s.*,rownum r from student s where rownum <= ?)ss where ss.r>?");
pst.setInt(1,pageNo*pageSize);
pst.setInt(2,(pageNo-1)*pageSize);
rs = pst.executeQuery();
while (rs.next()) {
student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setUsername(rs.getString("username"));
student.setPassword(rs.getString("password"));
student.setSex(rs.getInt("sex"));
student.setAge(rs.getInt("age"));
student.setBirthday(rs.getDate("birthday"));
student.setCreateTime(rs.getTimestamp("create_time"));
list.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.Close(con, pst, rs);
}
return list;
}
public void addMore(List<Student> list) {
Connection con = null;
PreparedStatement pst = null;
try {
con = DBUtil.getConnection();
pst = con.prepareStatement("insert into student(id,name,username,password,sex,age,birthday,create_time) values (studnet_0306.nextval,?,?,?,?,?,?,sysdate)");
for (int i =0;i<list.size();i++) {
Student student = list.get(i);
pst.setString(1, student.getName());
pst.setString(2, student.getUsername());
pst.setString(3, student.getPassword());
pst.setInt(4, student.getSex());
pst.setInt(5, student.getAge());
pst.setDate(6, new Date(student.getBirthday().getTime()));
//把sql添加到缓冲区
pst.addBatch();
if (i%500==0){
//批量执行缓冲区的sql
pst.executeBatch();
//清空缓冲区
pst.clearBatch();
}
}
//批量执行缓冲区的sql
pst.executeBatch();
//清空缓冲区
pst.clearBatch();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.Close(con, pst, null);
}
}
}
使用
public class Test6Dao {
public static void main(String[] args) {
Student stu1 = new Student(null, "李四2", "1244abc", "123aaaa", 0, 20, new Date(), null);
Student stu2 = new Student(221, "王八", "dasd", "sdwad", 0, 20, new Date(), new Timestamp(new Date().getTime()));
StudentDaoImpl s = new StudentDaoImpl();
// s.add(stu1);
// s.deleteById(3);
// s.update(stu2);
// System.out.println("id:" + s.queryById(223).getId() + "name:" + s.queryById(223).getName() + "username:" + s.queryById(223).getUsername() + "password:" + s.queryById(223).getPassword());
s.queryByIdPage(1, 10).stream().forEach(stu -> {
System.out.println("id:" + stu.getId() + "name:" + stu.getName() + "password:" + stu.getPassword() + "username:" + stu.getUsername() + "sex" + stu.getSex() + "age:" + stu.getAge());
});
}
}