public class JDBCutils {
public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
//1.通过本类的类加载器读取配置文件中的四个基本信息
InputStream is = ClassLoader.getSystemResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
//关闭连接和statement
public static void closeResource(PreparedStatement ps,Connection conn){
try {
if (ps!=null)
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (conn!=null)
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
增加数据
public void testInsert() {
PreparedStatement ps = null;
Connection conn = null;
try {
conn = JDBCutils.getConnection();
//4.预编译sql语句，返回PreparedStatement的实例
String sql="insert into student(Sno,Sname,Ssex,Sage,Sdept) values (?,?,?,?,?)";
ps = conn.prepareStatement(sql);
//5.填写占位符
ps.setString(1,"54200721");
ps.setString(2,"李佳祥");
ps.setString(3,"男");
ps.setInt(4,18);
ps.setString(5,"Java");
//6.执行操作
ps.execute();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//关闭资源
JDBCutils.closeResource(ps,conn);
}
修改数据库中的数据
public void testUpdate() {
Connection conn = null;
PreparedStatement ps = null;
try {
//获取连接
conn = JDBCutils.getConnection();
//预编译sql语句，返回PreparedStatement实例
String sql="update student set Sname=? where Sno=?";
ps = conn.prepareStatement(sql);
ps.setString(1,"李大磊");
ps.setString(2,"54200721");
//执行
ps.execute();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//关闭资源
JDBCutils.closeResource(ps,conn);
}
删除数据库表中的数据
//删除数据库中的数据
public void testDelete(){
Connection conn = null;
PreparedStatement ps = null;
try {
//获取连接
conn = JDBCutils.getConnection();
//编译sql语句，返回PreparedStatement实例
String sql="delete from student where Sname=?";
ps = conn.prepareStatement(sql);
ps.setString(1,"李大磊");
//执行操作
ps.execute();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//关闭资源
JDBCutils.closeResource(ps,conn);
}
通用的增删改操作
//通用增删改操作
public void update(String sql,Object...args){//sql中的占位符个数和数组的长度相等，所以用循环解决设置占位符的值
Connection conn = null;
PreparedStatement ps = null;
try {
//获取连接
conn = JDBCutils.getConnection();
//编译sql语句，返回preparedStatement实例
ps = conn.prepareStatement(sql);
//通过循环来设置占位符的值
for(int i=0;i
查询数据库中的数据
用Javabean将数据库中的数据封装成一个对象
package com.jdbc.learn.bean;
public class Student {
private String Sno;
private String Sname;
private String Ssex;
private int Sage;
private String Sdept;
public Student() {
}
public Student(String sno, String sname, String ssex, int sage, String sdept) {
Sno = sno;
Sname = sname;
Ssex = ssex;
Sage = sage;
Sdept = sdept;
}
public String getSno() {
return Sno;
}
public void setSno(String sno) {
Sno = sno;
}
public String getSname() {
return Sname;
}
public void setSname(String sname) {
Sname = sname;
}
public String getSsex() {
return Ssex;
}
public void setSsex(String ssex) {
Ssex = ssex;
}
public int getSage() {
return Sage;
}
public void setSage(int sage) {
Sage = sage;
}
public String getSdept() {
return Sdept;
}
public void setSdept(String sdept) {
Sdept = sdept;
}
@Override
public String toString() {
return "Student{" +
"Sno='" + Sno + ''' +
", Sname='" + Sname + ''' +
", Ssex='" + Ssex + ''' +
", Sage=" + Sage +
", Sdept='" + Sdept + ''' +
'}';
}
}
查询操作
public void testQuery1(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.获取连接
conn = JDBCutils.getConnection();
//2.编译sql语句，返回prepareStatement实例
String sql="select *from student where Sno=?";
ps = conn.prepareStatement(sql);
//3.设置占位符的值
ps.setString(1,"54200721");
//执行，并返回结果集
rs = ps.executeQuery();
if(rs.next()){//next():判断结果集的下一条数据，如果有数据返回true，并指向下一条数据
String Sno = rs.getString(1);
String Sname = rs.getString(2);
String Ssex = rs.getString(3);
int Sage = rs.getInt(4);
String Sdept = rs.getString(5);
//将数据分装在在一个类对象当中
Student student = new Student(Sno, Sname, Ssex, Sage, Sdept);
System.out.println(student);
}
} catch (Exception e) {
e.printStackTrace();
}
JDBCutils.closeResource(ps,conn,rs);
}
针对于student表的通用查询方法
public void testqueryStudent(){
String sql="select Sno,Sname,Ssex from student where Sno=?";
Student student = queryForStudent(sql, "201215121");
System.out.println(student);
}
//针对于student表的通用查询写法
public Student queryForStudent(String sql,Object...args){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.获取连接
conn = JDBCutils.getConnection();
//2.编译sql语句，返回prepareStatement实例
ps = conn.prepareStatement(sql);
//3.通过循环设置占位符的值
for(int i=0;i
注：结果集是存储数据的、结果集的元数据是存储修饰数据的内容，比如 列名、列数等
