模板代码 :
Connection conn = null;
Statement st=null;
ResultSet rs = null;
try {
//获得Connection
//创建Statement
//处理查询结果ResultSet
} finally {
//释放资源ResultSet, Statement,Connection
}
创建:
增加对应SQL的INSERT,返回增加成功的行(记录)数
conn = getConnection();
Statement st = conn.createStatement();
String sql=“insert into user(name, age,regist_date )” + “values(‘name’, 10, now())”;
int i = st.executeUpdate(sql);
/ /i为插入的记录数
读取:
读取(查询)对应SQL的SELECT,返回查询结果
conn = getConnection();
st = conn.createStatement();
String sql = "select id, name, age,regist_date from user";
rs = st.executeQuery(sql);
while (rs.next()) {
System.out.print(rs.getInt("id") + " \t\t ");
System.out.print(rs.getString("name") + " \t\t ");
System.out.print(rs.getInt("age") + " \t\t ");
System.out.print(rs.getTimestamp("regist_date") + " \t\t ");
System.out.println();
} (ps:rs.get_*()括号内也可填写序列号,按照select中的顺序填写即可)
更新:
更新(修改)对应SQL的UPDATE,返回被修改的行(记录)数
conn = getConnection();
Statement st = conn.createStatement();
String sql=“update person set name='new name‘”;
int i = st.executeUpdate(sql);
//i为符合条件的记录数
删除:
删除对应SQL的DELETE,返回被删除的行(记录)数
conn = getConnection();
Statement st = conn.createStatement();
String sql=“delete from user where id=1”;
int i = st.executeUpdate(sql);
//i为删掉的记录数
CRUD
总结
1、增、删、改用Statement.executeUpdate来完成,返回整数(匹配的记录数),这类操作相对简单。
2、查询用Statement.executeQuery来完成,返回的是ResultSet对象,ResultSet中包含了查询的结果;查询相对与增、删、改要复杂一些,因为有查询结果要处理。
完整的小例子:
import java.sql.*;
public class CRUD {
public static void main(String[] args) throws SQLException {
// TODO Auto-generated method stub
System.out.println(delate());
//read();
}
static int create() throws SQLException{
Connection conn=null;
Statement st=null;
ResultSet rs=null;
int i;
try{
conn=jdbcUtils.getConnection();
st=conn.createStatement();
String sql="insert into T_Users(name,birthday,money) values('qq','20121225','200')";
i=st.executeUpdate(sql);
}
finally{
jdbcUtils.free(conn, st, rs);
}
return i;
}
static void read() throws SQLException{
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try{
conn=jdbcUtils.getConnection();
st=conn.createStatement();
rs=st.executeQuery("select id ,name,birthday,money from T_Users where name='www'");
while(rs.next()){
System.out.println(rs.getObject("name")+" "+rs.getObject("money"));
}
}
finally{
jdbcUtils.free(conn, st, rs);
}
}
static int update() throws SQLException{
Connection conn=null;
Statement st=null;
ResultSet rs=null;
int i;
try{
conn=jdbcUtils.getConnection();
st=conn.createStatement();
String sql="update T_Users set name='www' where name='qq'";
i=st.executeUpdate(sql);
}
finally{
jdbcUtils.free(conn, st, rs);
}
return i;
}
static int delate() throws SQLException{
Connection conn=null;
Statement st=null;
ResultSet rs=null;
int i;
try{
conn=jdbcUtils.getConnection();
st=conn.createStatement();
String sql="delete from T_Users where id<5";
i=st.executeUpdate(sql);
}
finally{
jdbcUtils.free(conn, st, rs);
}
return i;
}
}