网上书店源码
package user;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.sql.PreparedStatement;
import com.ConnDB;
import java.sql.*;
import java.util.*;
import com.ConnDB;
public class UserDB extends ConnDB{
private ArrayList user;
public UserDB () throws Exception
{
}
public boolean userExist(String username){
boolean occupied=true;
try
{
ConnDB.initialize(); // create database connection PreparedStatement preparedStatement = conn.prepareStatement ( "select userid from Buser where username=?"); preparedStatement.setString (1, username);
ResultSet resultSet = preparedStatement.executeQuery ();
if(!resultSet.next())
occupied=false;
preparedStatement.close ();
ConnDB.terminate();
}
catch(SQLException e){
e.printStackTrace();
}
return occupied;
}
public boolean isValidUser (String username, String password)
{
boolean isValid=false;
try
{
ConnDB.initialize(); // create database connection
PreparedStatement preparedStatement = conn.prepareStatement (
"SELECT username, password FROM Buser WHERE username=? and password=?"); preparedStatement.setString (1, username);
preparedStatement.setString (2, password);
ResultSet resultSet = preparedStatement.executeQuery ();
if (resultSet.next ())
{
isValid=true;
preparedStatement.close ();
ConnDB.terminate();
}
else
{
preparedStatement.close ();
ConnDB.terminate();
//return isValid;
}
}
catch (SQLException e)
{
//return isValid;
}
return isValid;
}
public boolean find(String key)
{
boolean gotIt=false;
try
{
ConnDB.initialize(); // create database connection PreparedStatement preparedStatement = conn.prepareStatement ( "SELECT username, password FROM Buser WHERE username = ?"); preparedStatement.setString (1, key);
ResultSet resultSet = preparedStatement.executeQuery ();
if (resultSet.next ())
{
gotIt=true;
preparedStatement.close ();
ConnDB.terminate();
}
else
{
preparedStatement.close ();
ConnDB.terminate();
//return isValid;
}
}
catch (SQLException e)
{
//return gotIt;
}
return gotIt;
}
public User getUser (int id)
{
try
{
ConnDB.initialize(); // create database connection
PreparedStatement preparedStatement = conn.prepareStatement (
"SELECT userid, username, rname, password, email, address, postdate, tel " +
"FROM Buser WHERE userId = ?");
preparedStatement.setInt (1, id);
ResultSet resultSet = preparedStatement.executeQuery ();
if (resultSet.next ())
{
User users = new User
(
resultSet.getInt (1), resultSet.getString (2), resultSet.getString (3), resultSet.getString (4),
resultSet.getString (5), resultSet.getString (6), resultSet.getString (7), resultSet.getString (8)
);
preparedStatement.close ();
ConnDB.terminate();
return users;
}
else
{
preparedStatement.close ();
return null;
}
}
catch (SQLException e)
{
ConnDB.terminate();
return null;
}
}
public User getUser (String username)
{
try
{
ConnDB.initialize(); // create database connection
PreparedStatement preparedStatement = conn.prepareStatement (
"SELECT userid, username, rname, password, email, address, postdate, tel " +
"FROM Buser WHERE username = ?");
preparedStatement.setString (1, username);
ResultSet resultSet = preparedStatement.executeQuery ();
if (resultSet.next ())
{
User users = new User
(
resultSet.getInt (1), resultSet.getString (2), resultSet.getString (3), resultSet.getString (4),
resultSet.getString (5), resultSet.getString (6), resultSet.getString (7), resultSet.getString (8)
);
preparedStatement.close ();
ConnDB.terminate();
return users;
}
else
{
preparedStatement.close ();
ConnDB.terminate();
return null;
}
}
catch (SQLException e)
{
ConnDB.terminate();
return null;
}
}
public int addUsers (User users)
{
System.out.println("**");
int rowsAffected = 0;
try
{
ConnDB.initialize(); // create database connection
PreparedStatement preparedStatement = conn.prepareStatement (
"INSERT INTO Buser (username, rname, password, email, address, postdate, tel) " + "VALUES (?, ?, ?, ?, ?, ?, ?)");
preparedStatement.setString (1, users.getUsername ());
preparedStatement.setString (2, users.getRname ());
preparedStatement.setString (3, users.getPassword());
preparedStatement.setString (4, users.getEmail());
preparedStatement.setString (5, users.getAddress());
preparedStatement.setString (6, users.getPostdate());
preparedStatement.setString (7, users.getT el());
//判断是否重复添加
if(find(users.getUsername ())){
rowsAffected =2;
System.out.println("exist username");
}
else{
rowsAffected = preparedStatement.executeUpdate ();
System.out.println(rowsAffected + "add");
}
preparedStatement.close ();
ConnDB.terminate();
}
catch (SQLException e)
{
ConnDB.terminate();
return 0;
}
return rowsAffected;
}
public int deleteUser (int id)
{
int rowsAffected = 0;
try
{
ConnDB.initialize(); // create database connection
PreparedStatement preparedStatement = conn.prepareStatement ("DELETE FROM Buser WHERE userid = ?");
preparedStatement.setInt (1, id);
rowsAffected = preparedStatement.executeUpdate ();
preparedStatement.close ();
ConnDB.terminate();
}
catch (SQLException e)
{
ConnDB.terminate();
return 0;
}
return rowsAffected;
}
public int modifyUser (User users)
{
int rowsAffected = 0;
try
{
ConnDB.initialize(); // create database connection
PreparedStatement preparedStatement = conn.prepareStatement (
"UPDATE Buser SET username=?, rname=?, password=?, email=?, address=?, postdate=?, tel=? " +
"WHERE userid =?");
preparedStatement.setString (1, users.getUsername ());
preparedStatement.setString (2, users.getRname ());
preparedStatement.setString (3, users.getPassword());
preparedStatement.setString (4, users.getEmail());
preparedStatement.setString (5, users.getAddress());
preparedStatement.setString (6, users.getPostdate());
preparedStatement.setString (7, users.getT el());
preparedStatement.setInt (8, users.getId ());
rowsAffected = preparedStatement.executeUpdate ();
ConnDB.terminate();
}
catch (SQLException e)
{
ConnDB.terminate();
return 0;
}
return rowsAffected;
}
public Collection getUser ()
{
user = new ArrayList ();
try
{
ConnDB.initialize(); // create database connection
PreparedStatement preparedStatement = conn.prepareStatement (
"SELECT userid, username, rname, password, email, address, postdate, tel " +
"FROM Buser");
ResultSet resultSet = preparedStatement.executeQuery ();
while (resultSet.next ())
{
User users = new User
(
resultSet.getInt (1), resultSet.getString (2), resultSet.getString (3), resultSet.getString (4),
resultSet.getString (5), resultSet.getString (6), resultSet.getString (7), resultSet.getString (8)
);
user.add(users);
}
}
catch (SQLException e)
{
return null;
}
ConnDB.terminate();
//Collections.sort(user);
return user;
}
public boolean isModify(String key,int id)
{
boolean modifyIt=false;
try
{
ConnDB.initialize(); // create database connection PreparedStatement preparedStatement = conn.prepareStatement ( "SELECT userid FROM Buser WHERE username = ? and userd <> ?"); preparedStatement.setString (1, key);
preparedStatement.setInt (2, id);
ResultSet resultSet = preparedStatement.executeQuery ();
if (resultSet.next ())
{
modifyIt=true;
preparedStatement.close ();
ConnDB.terminate();
}
else
{
preparedStatement.close ();
ConnDB.terminate();
//return isValid;
}
}
catch (SQLException e) {
ConnDB.terminate();
//return gotIt;
}
return modifyIt;
}
}