从屌丝到架构师的飞越（数据库篇）-JDBC封装
一.介绍
一些小型的项目，有时不想使用框架如MyBatis，Hibernate等开发时，往往在数据持久化时操作比较繁琐，以下提供了对JDBC的封装类，简化JDBC操作。
为了更客观展示JDBC，我们通过一般的JDBC连接数据库的增删改查来对比。
JDBC连接数据库操作通常的做法都是先建立一个公共类，来配置数据库信息，加载驱动等。
二.知识点介绍
1、JDBC封装类
2、JDBC查询操作
三.上课对应视频说明文档
1、JDBC封装类（注意：一定要加载驱动jar包）
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcFZ {
private static final String MYSQL_D = "org.gjt.mm.mysql.Driver";
private static final String MYSQL_A = "jdbc:mysql://localhost:3306/ww";
private static final String MYSQL_U = "root";
private static final String MYSQL_P = "root";
private static Connection conn;
private JdbcFZ() {
}
public static Connection getConnection() {
try {
Class.forName(MYSQL_D).newInstance();
conn = DriverManager.getConnection(MYSQL_A, MYSQL_U, MYSQL_P);
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void close3(ResultSet rs, PreparedStatement ps,
Connection conn) {
try {
rs.close();
ps.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void close2(PreparedStatement ps,
Connection conn) {
try {
ps.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
2、JDBC查询操作
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class StudentsCZ {
private Connection conn;
private PreparedStatement ps;
private ResultSet rs;
public static void main(String[] args) {
// TODO Auto-generated method stub
StudentsCZ s = new StudentsCZ();
// s.delete();
//s.update();
s.insert();
s.select();
}
//查询students表中的所有记录
public void select() {
String sql = "select * from students";
try {
conn = JdbcFZ.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("name") + " "
+ rs.getInt("age"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JdbcFZ.close3(rs, ps, conn);
}
}
//删除students中的指定id记录
public void delete() {
String sql = "delete from students where id=?";
try {
conn = JdbcFZ.getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, 2);
ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JdbcFZ.close2(ps, conn);
}
}
//通过id,修改students表中的name,age记录
public void update() {
String sql = "update students set name=?,age=? where id=?";
try {
conn = JdbcFZ.getConnection();
ps = conn.prepareStatement(sql);
ps.setString(1, "六六");
ps.setInt(2, 28);
ps.setInt(3, 3);
ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JdbcFZ.close2(ps, conn);
}
}
//增加stundens表中的name,age记录
public void insert() {
String sql = "insert into students (name,age)values(?,?)";
try {
conn = JdbcFZ.getConnection();
ps = conn.prepareStatement(sql);
ps.setString(1, "七七");
ps.setInt(2, 22);
ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JdbcFZ.close2(ps, conn);
}
}
}
