package com.jdbc.day01;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class CRUD {
@Test
public void create(){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
String sql = "create table user(id int not null auto_increment,username varchar(255),password varchar(255),sex varchar(255),age INTEGER,primary key(id))";
JDBCUtils jdbc = JDBCUtils.getInstance();
try {
//创建连接
conn=jdbc.getConnection();
//创建执行sql语句
st=conn.createStatement();
//执行sql语句
st.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
//关闭资源
jdbc.free(conn, st, rs);
}
}
@Test
public void insert(){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
String sql = "insert into user(username,password,sex,age)values('小f','123456','1','18')";
JDBCUtils jdbc = JDBCUtils.getInstance();
try {
conn = jdbc.getConnection();
st = conn.createStatement();
st.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbc.free(conn, st, rs);
}
}
@Test
public void read(){
Connection conn=null;
Statement st = null;
ResultSet rs=null;
JDBCUtils jdbc = JDBCUtils.getInstance();
String sql = "select username,password,sex,age from user";
try {
conn=jdbc.getConnection();
st = conn.createStatement();
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getObject("username")+"\t"
+rs.getObject("password")+"\t"
+rs.getObject("sex")+"\t"
+rs.getObject("age")+"\t");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbc.free(conn, st, rs);
}
}
@Test
public void update(){
Connection conn=null;
Statement st = null;
ResultSet rs=null;
JDBCUtils jdbc = JDBCUtils.getInstance();
String sql = "update user set age = age+1 where id>3";
try {
conn=jdbc.getConnection();
st = conn.createStatement();
st.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbc.free(conn, st, rs);
}
}
@Test
public void delete(){
Connection conn=null;
Statement st = null;
ResultSet rs=null;
JDBCUtils jdbc = JDBCUtils.getInstance();
String sql = "delete from user where id>5";
try {
conn=jdbc.getConnection();
st = conn.createStatement();
st.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbc.free(conn, st, rs);
}
}
/*-----------------------------------------------------PreparedStatement防止sql注入,预编译效率高---------------------------*/
@Test
public void psUpdate(){
Connection conn=null;
PreparedStatement ps = null;
ResultSet rs=null;
JDBCUtils jdbc = JDBCUtils.getInstance();
String sql = "update user set password=? where id=?";
try {
conn=jdbc.getConnection();
ps = conn.prepareStatement(sql);
//为每个问号赋值,1和2分别代表第一个问号和第二个问号
ps.setString(1, "654321");
ps.setString(2, "5");
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbc.free(conn, ps, rs);
}
}
}
JDBC简单的CRUD操作
最新推荐文章于 2022-11-25 18:32:27 发布