/** * PreparedStatement是在创建pstm的时候就给它传一个动态的sql，参数是通过pstm设置的。执行时，只需要空执行一下就可以. * @author Administrator * */ public class PreparedStatementCRUDtest {
/** * 操作表jdbc_users * @param args */ public static void main(String[] args) {
User u=
new User();
u.setId(21);
u.setName(
"statement");
u.setPasswd(
"yf123");
u.setPhone(
"13821930");
u.setEmail(
"yf@163.com");
//insert(u); //delete(42); //reset(u); System.out.println(getById(21));
}
/**增*/ public static void insert(User user){
Connection conn=
null;
PreparedStatement pstmt=
null;
try {
Class.forName(
"oracle.jdbc.driver.OracleDriver");
//conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL10","scott","yf123"); conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:ORCL10",
"SCOTT",
"yf123");
String sql=
"insert into jdbc_users values(?,?,?,?,?)";
pstmt=conn.prepareStatement(sql);
//PreparedStatement创建时就传过去一个sql语句,这样就可以预编译 /**然后设置sql中好占位符的值,这里是动态的传参数*/ pstmt.setInt(1, user.getId());
pstmt.setString(2, user.getName());
pstmt.setString(3, user.getPasswd());
pstmt.setString(4, user.getPhone());
pstmt.setString(5, user.getEmail());
/**设置好后，就全封装到pstm里了,只要空执行就可以了*/ pstmt.execute();
}
catch (Exception e) {
e.printStackTrace();
}
finally{
if(pstmt!=
null){
try{pstmt.close();}
catch(Exception e){}}
if(conn!=
null){
try{conn.close();}
catch(Exception e){}}
}
}
/**删*/ public static void delete(Integer id){
Connection conn=
null;
PreparedStatement pstmt=
null;
try {
Class.forName(
"oracle.jdbc.driver.OracleDriver");
//conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL10","scott","yf123"); conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:ORCL10",
"SCOTT",
"yf123");
String sql=
"delete from jdbc_users where id=?";
/**PreparedStatement创建时就传过去一个sql语句,这样就可以预编译*/ pstmt=conn.prepareStatement(sql);
/**然后设置sql中好占位符的值,这里是动态的传参数*/ pstmt.setInt(1, id);
/**设置好后，就全封装到pstm里了,只要空执行就可以了*/ pstmt.execute();
}
catch (Exception e) {
e.printStackTrace();
}
finally{
if(pstmt!=
null){
try{pstmt.close();}
catch(Exception e){}}
if(conn!=
null){
try{conn.close();}
catch(Exception e){}}
}
}
/**改*/ public static void reset(User u){
Connection conn=
null;
PreparedStatement pstmt=
null;
try {
Class.forName(
"oracle.jdbc.driver.OracleDriver");
//conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL10","scott","yf123"); conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:ORCL10",
"SCOTT",
"yf123");
String sql=
"update jdbc_users set name=?,passwd=?,phone=?,email=? where id=?";
/**PreparedStatement创建时就传过去一个sql语句,这样就可以预编译*/ pstmt=conn.prepareStatement(sql);
/**然后设置sql中好占位符的值,这里是动态的传参数*/ pstmt.setString(1, u.getName());
pstmt.setString(2, u.getPasswd());
pstmt.setString(3, u.getPhone());
pstmt.setString(4, u.getEmail());
pstmt.setInt(5, u.getId());
/**设置好后，就全封装到pstm里了,只要空执行就可以了*/ pstmt.execute();
}
catch (Exception e) {
e.printStackTrace();
}
finally{
if(pstmt!=
null){
try{pstmt.close();}
catch(Exception e){}}
if(conn!=
null){
try{conn.close();}
catch(Exception e){}}
}
}
/**查*/ public static User getById(Integer id){
Connection conn=
null;
PreparedStatement pstmt=
null;
ResultSet rs=
null;
User u=
null;
try {
Class.forName(
"oracle.jdbc.driver.OracleDriver");
//conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL10","scott","yf123"); conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:ORCL10",
"SCOTT",
"yf123");
String sql=
"select * from jdbc_users where id=?";
pstmt=conn.prepareStatement(sql);
//Statement创建时就是一个空的执行器 /**在execute或者executeQuery时执行死的sql语句*/ /**这只能是拼好的字符串,而不能动态的传参数,并且在数据库中每次肯定穿的是不同的sql语句，因此每次都要解析编译*/ pstmt.setInt(1, id);
rs=pstmt.executeQuery();
while(rs.next()){
u=
new User();
u.setId(rs.getInt(
"id"));
u.setName(rs.getString(
"name"));
u.setPhone(rs.getString(
"phone"));
u.setPasswd(rs.getString(
"passwd"));
u.setEmail(rs.getString(
"email"));
}
}
catch (Exception e) {
e.printStackTrace();
}
finally{
if(pstmt!=
null){
try{pstmt.close();}
catch(Exception e){}}
if(conn!=
null){
try{conn.close();}
catch(Exception e){}}
}
return u;
}
}