JDBC实现数据库的几种基本操作（查询，分页查询，根据关键字进行查询以及插入数据）

public class CreateTable {
/**
* 向表中插入数据
* 
* @param sql
* @param user
*/
public void insertData(String sql, User user) {
DriverMangement ds = DriverMangement.getInstance();
Connection connection = null;
PreparedStatement ps = null;
try {
connection = ds.getConnection();
ps = connection.prepareStatement(sql);
ps.setInt(1, user.getId());
ps.setString(2, user.getName());
ps.setString(3, user.getPassword());
ps.setString(4, user.getSex());
ps.setString(5, user.getTelphone());
ps.setString(6, user.getPassword());
ps.setString(7, user.getEmail());
ps.executeUpdate();
ps.close();
connection.close();


} catch (Exception e) {


}
}


/**
* 查询所有用户的信息
* 
* @return
* @throws SQLException
*/
public List<User> searchInfo() throws SQLException {
DriverMangement ds = DriverMangement.getInstance();
Connection connection = null;
PreparedStatement ps = null;
String sql = "select * from user";


ResultSetToObject handler = new ResultSetToObject() {
/**
* 把ResultSet里面的值转换成某个具体的对象
*/
@Override
public Object convertToObject(ResultSet rs) throws SQLException {
User user = new User();
int id = rs.getInt("id");
String name = rs.getString("name");
String password = rs.getString("password");
String sex = rs.getString("telphone");
String telphone = rs.getString("name");
String photo = rs.getString("photo");
String email = rs.getString("email");


user.setId(id);
user.setName(name);
user.setPassword(password);
user.setPhoto(photo);
user.setEmail(email);
user.setSex(sex);
user.setTelphone(telphone);
return user;
}
};


List data = new Vector();


connection = ds.getConnection();
ps = connection.prepareStatement(sql);
Statement st = (Statement) connection.createStatement();


ResultSet resultSet = ps.executeQuery();
while (resultSet.next()) {


Object val = handler.convertToObject(resultSet);
data.add(val);


}
ps.close();
connection.close();


System.out.println("所有学生的信息：" + data);
return data;
}


/**
* 根据姓名和密码进行查询
* 
* @param name
* @param password
* @return
*/
public List<User> SearchData(String name, String password) {
DriverMangement ds = DriverMangement.getInstance();
Connection connection = null;
PreparedStatement ps = null;
String sql = "select * from user where name=? and password=?";
ResultSetToObject handler = new ResultSetToObject() {


@Override
public Object convertToObject(ResultSet rs) throws SQLException {
User user = new User();
int id = rs.getInt("id");
String name = rs.getString("name");
String password = rs.getString("password");
String sex = rs.getString("telphone");
String telphone = rs.getString("name");
String photo = rs.getString("photo");
String email = rs.getString("email");
user.setId(id);
user.setName(name);
user.setPassword(password);
user.setPhoto(photo);
user.setEmail(email);
user.setSex(sex);
user.setTelphone(telphone);
return user;
}
};
List<User> data = new Vector<User>();
try {
connection = ds.getConnection();
ps = connection.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, password);
ResultSet resultSet = ps.executeQuery();
while (resultSet.next()) {
Object val = new Object();
val = handler.convertToObject(resultSet);
data.add((User) val);
}
ps.close();
connection.close();


} catch (Exception e) {


}
System.out.println(data);
return data;
}


/**
* 判断数据库中该用户名是否存在
* 
* @param username
* @return
*/
public static boolean userExsist(String username) {
DriverMangement ds = DriverMangement.getInstance();
String sql = "select * from user where name=?";
Connection connection = null;
PreparedStatement ps = null;
Boolean bool = true;
try {


connection = ds.getConnection();
ps = connection.prepareStatement(sql);
ps.setString(1, username);
ResultSet rs = ps.executeQuery();


if (rs.next()) {


User user = new User();
user.setId(rs.getInt("id"));
user.setEmail(rs.getString("email"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setPhoto(rs.getString("photo"));
user.setSex(rs.getString("sex"));
user.setTelphone(rs.getString("telphone"));
System.out.println(user);
return true;
}
rs.close();
ps.close();
connection.close();
} catch (Exception e) {


}
return false;


}

/**
* 分页显示信息
* @param sql
* @param pagesize
* @param page
* @return
* @throws SQLException
*/
public CachedRowSet showByPage(String sql,int pagesize,int page) throws SQLException
{
DriverMangement ds=DriverMangement.getInstance();
Connection connection=null;
PreparedStatement ps=null;
CachedRowSet cRowSet=null;
try {
connection=ds.getConnection();
ps=connection.prepareStatement(sql);
ResultSet rSet=ps.executeQuery();
RowSetFactory rFactory=RowSetProvider.newFactory();
cRowSet=rFactory.createCachedRowSet();

cRowSet.setPageSize(pagesize);
cRowSet.populate(rSet,(page-1)*pagesize+1);
rSet.close();
ps.close();

} catch (Exception e) {
// TODO: handle exception
}
finally
{
if(connection!=null)
{
connection.close();
}
}
return cRowSet;





}


public static User getUser() {
User user = new User();
user.setId(005);
user.setName("zhansgan");
user.setPassword("123456");
user.setSex("女");
user.setTelphone("18444441111");
user.setPhoto("1.jpeg");
user.setEmail("[e-mail]");
return user;


}


public static void main(String[] args) throws SQLException {
CreateTable table = new CreateTable();
User user = getUser();
String sql = "insert into user(id,name,password,sex,telphone,photo,email)  values(?,?,?,?,?,?,?)";
String name = "zhansgan";
String pass = "123456";
String sql1="select * from user";
// boolean bool = table.userExsist("zhansgan");
// if (bool) {
// System.out.println("用户名已存在");
// } else {
// System.out.println("对不起，你所查找的用户不存在");
// }
// table.insertData(sql, user);
// table.SearchData(name, pass);
// table.searchInfo();

   CachedRowSet cachedRowSet=table.showByPage(sql1, 4, 2);
   while(cachedRowSet.next())
{
    System.out.println(cachedRowSet.getInt("id")+"\t"
                    +cachedRowSet.getString("name")+"\t"
                     +cachedRowSet.getString("password")+"\t"
                    +cachedRowSet.getString("sex")+"\t"
                    +cachedRowSet.getString("email")+"\t"
                      +cachedRowSet.getString("telphone")+"\t"                     
                     +cachedRowSet.getString("photo"));
}
}


}