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("ahnu12345@163.com");
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"));
}
}
}
/**
* 向表中插入数据
*
* @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("ahnu12345@163.com");
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"));
}
}
}