一、JDBC使用流程
代码:
public class TestJdbc {
public static void main(String[] args){
String url = "jdbc:mysql://localhost/JDBC?useSSL=false";
String user = "root";
Sting password = "123456";
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//获取数据库连接对象
Connection conn = DriverManager.getConnection(url, user, password);
//获取sql命令对象(编译和发送sql命令给数据库)
Statement stmt = conn.createStatement();
//创建sql命令
String sql = "insert into user values(1, 'lizhi')";
//执行sql
int i = stmt.excuteUpdate(sql);
//关闭资源
stmt.colse();
conn.colse();
}
二、开启事务管理
public class TestJdbc {
public static void main(String[] args){
String url = "jdbc:mysql://localhost/JDBC?useSSL=false";
String user = "root";
String password = "123456";
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, user, password);
//关闭自动提交事务
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
String sql = "insert into user values(1, 'lizhi')";
try{
int i = stmt.excuteUpdate(sql);
//执行正常提交事务
conn.commit();
} catch (Exception e){
//出现异常,进行回滚
conn.rollback();
}
stmt.colse();
conn.colse();
}
三、JDBC查询操作
public class TestJdbc {
public static void main(String[] args){
String url = "jdbc:mysql://localhost/JDBC?useSSL=false";
String user = "root";
String password = "123456";
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement();
//创建sql命令
String sql = "select * from Person";
//查询返回一个ResultSet结果集
ResultSet sets=statement.executeQuery(sql);
//将结果集转存到List集合中,方便操作数据
ArrayList<Person> arr=new ArrayList<>();
//循环取存
while(sets.next()){//如果存在则返回true
Person p=new Person();
p.id=sets.getInt("id");
p.name=sets.getString("name");
arr.add(p);
}
stmt.colse();
conn.colse();
}
四、JDBC防止sql注入和预编译的 PrepareStatement
优点:
public class TestJdbc {
public static void main(String[] args){
String url = "jdbc:mysql://localhost/JDBC?useSSL=false";
String user = "root";
String password = "123456";
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, user, password);
//先定义sql语句
String sql = "select * from user where username=? and password=?";
//sql放入PreparedStatement,预编译sql
PreparedStatement ps = conn.prepareStatement(sql);
//给?赋值, JDBC所有下标都从1开始
ppstate.setString(1, "lizhi");//第一个?赋username
ppstate.setString(2, "nanjing");//第二个?赋userpassword
//执行SQL语句
result = ppstate.executeQuery();
stmt.colse();
conn.colse();
}
五、JDBC封装类
public class DBUtil {
//获得连接对象
public static Connection getConnection(){
String url = "jdbc:mysql://localhost/JDBC?useSSL=false";
String user = "root";
String password = "123456";
Connection conn = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//关闭连接
public static void closeConnection(Connection conn, Statement st, PreparedStatement pst){
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pst != null){
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试封装类:
public class JDBC_test {
public static void main(String[] args) {
Connection conn = DBUtil.getConnection();
PreparedStatement pst = null;
ResultSet re = null;
try {
String sql = "insert into person values('李志', '25', '南京')";
pst = conn.prepareStatement(sql);
pst.execute();
String sql1 = "select * from person";
pst = conn.prepareStatement(sql1);
pst.execute();
re = pst.executeQuery(sql1);
while (re.next()){
System.out.print("name:" + re.getString(1) + " age:" +re.getString(2) + " address:" + re.getString(3) +"\n");
}
} catch (SQLException e) {
e.printStackTrace();
}
DBUtil.closeConnection(conn, null, pst);
}
}