package com.mine.web;
import com.mine.pojo.User;
import com.mine.service.UserService;
import com.mine.service.UserServiceImpl;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Scanner;
/**
* 六个小需求
* 1)register function
* 2)login function
* 3)Query the information list for everyone
* 4)query personal information by id
* 5)update function
* 6)delete function
*
* 分层思想,分3层:
* 表现层,com.mine.web
* 服务层,com.mine.service
* 数据访问层,com.mine.dao
*
* 用户信息,com.mine.pojo
* 测试用例,com.mine.Test
* 以封装好的连接数据库方法(部分)
*
*
*/
public class WebTest {
private static Scanner sc=new Scanner(System.in);
private static UserService userService=new UserServiceImpl();
public static void main(String[] args) throws Exception{
System.out.println("===================welcome to the VIP ROOM===================");
System.out.println("please input your choice:");
System.out.println("1)register function");
System.out.println("2)login function");
System.out.println("3)Query the information list for everyone");
System.out.println("4)query personal information by id");
System.out.println("5)update function");
System.out.println("6)delete function");
String choiceNum=sc.nextLine();
int num = Integer.parseInt(choiceNum);
User user=new User();
switch (num){
case 1:
System.out.println("please input your number:");
int id=sc.nextInt();
sc.nextLine();
System.out.println("please input your username:");
String username=sc.nextLine();
System.out.println("please input your password:");
String password=sc.nextLine();
System.out.println("please input your address:");
String address=sc.nextLine();
System.out.println("please input your state:");
int state=sc.nextInt();
user.setId(id);
user.setUsername(username);
user.setPassword(password);
user.setAddress(address);
user.setState(state);
Date date=new Date();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String time=sdf.format(date);
user.setTime(time);
int i = userService.addUser(user);
if (i>0){
System.out.println("Congratulation!!Registered successfully!!!");
}else {
System.out.println("Sorry!Fail to register!!");
}
break;
case 2:
System.out.println("please input your account:");
String account=sc.nextLine();
System.out.println("please input your password:");
String psw=sc.nextLine();
User user1 = userService.login(account, psw);
if (user1.getUsername()!=null&&user1.getPassword()!=null){
System.out.println("Congratulation!Welcome the '"+user1.getUsername()+"' to THE VIP ROOM!!!");
}else {
System.out.println("Sorry!You can't enter THE VIP ROOM!!");
}
case 3:
List<User> userList = userService.getUserList();
for (User user2 : userList) {
System.out.println("Num"+user2.getId()+"---Name:"+user2.getUsername()+"---Address:"+user2.getAddress());
System.out.println();
}
break;
case 4:
System.out.println("input your id:");
int i1 = sc.nextInt();
User user2 = userService.byID(i1);
System.out.println(user2);
break;
case 5:
System.out.println("please input your number:");
int id1=sc.nextInt();
sc.nextLine();
System.out.println("please input your username:");
String username1=sc.nextLine();
System.out.println("please input your password:");
String password1=sc.nextLine();
System.out.println("please input your address:");
String address1=sc.nextLine();
System.out.println("please input your state:");
int state1=sc.nextInt();
user.setId(id1);
user.setUsername(username1);
user.setPassword(password1);
user.setAddress(address1);
user.setState(state1);
Date date1=new Date();
SimpleDateFormat sdf1=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String time1=sdf1.format(date1);
user.setTime(time1);
int i2 = userService.updateUser(user);
if (i2>0){
System.out.println("Congratulation!!Modify successfully!");
}else {
System.out.println("HA HA !!Change failed!!");
}
break;
case 6:
System.out.println("Enter the ID of the user you want to delete:");
int i3 = sc.nextInt();
int i4 = userService.deleteUser(i3);
if (i4>0){
System.out.println("DELETE successfully!!");
}else {
System.out.println("Sorry!!You haven't deleted it yet!");
}
break;
default:
break;
}
}
}
package com.mine.dao;
import com.mine.pojo.User;
import java.util.List;
public interface UserDao {
int addUser(User user) throws Exception;
User login(String uname,String psw) throws Exception;
List<User> getUserList() throws Exception;
User byID(int i) throws Exception;
int updateUser(User user) throws Exception;
int deleteUser(int i) throws Exception;
}
package com.mine.dao;
import com.mine.pojo.User;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class UserDaoImpl implements UserDao {
@Override
public int addUser(User user) throws Exception {
String sql="INSERT INTO tb_mine (id,username,password,address,state,creattime) VALUES(?,?,?,?,?,?)";
return JDBCTools.MyExecutePrepareUpdate(sql,JDBCTools.getConnection(),user);
}
@Override
public User login(String uname,String psw) throws Exception {
User user=new User();
String sql="SELECT * from tb_mine WHERE username='" + uname + "' and password='" + psw + "'";
ResultSet resultSet = JDBCTools.MyExecuteQuery(sql, JDBCTools.getConnection());
while (resultSet.next()){
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
user.setAddress(resultSet.getString("address"));
user.setState(resultSet.getInt("state"));
user.setTime(resultSet.getString("creattime"));
}
return user;
}
@Override
public List<User> getUserList() throws Exception{
List<User> userList=new ArrayList<User>();
String sql="select * from tb_mine";
ResultSet resultSet = JDBCTools.MyExecuteQuery(sql, JDBCTools.getConnection());
while (resultSet.next()){
User user=new User();
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
user.setAddress(resultSet.getString("address"));
user.setState(resultSet.getInt("state"));
user.setTime(resultSet.getString("creattime"));
userList.add(user);
}
return userList;
}
@Override
public User byID(int i) throws Exception{
User user=new User();
String sql="SELECT * FROM tb_mine WHERE id="+i+"";
ResultSet resultSet = JDBCTools.MyExecuteQuery(sql, JDBCTools.getConnection());
while (resultSet.next()){
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
user.setAddress(resultSet.getString("address"));
user.setState(resultSet.getInt("state"));
user.setTime(resultSet.getString("creattime"));
}
return user;
}
@Override
public int updateUser(User user) throws Exception{
String sql="UPDATE tb_mine SET id="+user.getId()
+",username='"+user.getUsername()
+"',`password`='"+user.getPassword()
+"',address='"+user.getAddress()
+"',state="+user.getState()+",creattime='"+user.getTime()
+"'where id='"+user.getId()+"'";
return JDBCTools.MyExecuteUpdate(sql,JDBCTools.getConnection());
}
@Override
public int deleteUser(int i) throws Exception {
String sql="DELETE FROM tb_mine WHERE id="+i+"";
return JDBCTools.MyExecuteUpdate(sql,JDBCTools.getConnection());
}
}
package com.mine.service;
import com.mine.pojo.User;
import java.util.List;
public interface UserService {
int addUser(User user) throws Exception;
User login(String uname,String psw) throws Exception;
List<User> getUserList() throws Exception;
User byID(int i) throws Exception;
int updateUser(User user) throws Exception;
int deleteUser(int i) throws Exception;
}
package com.mine.service;
import com.mine.dao.UserDao;
import com.mine.dao.UserDaoImpl;
import com.mine.pojo.User;
import java.util.List;
public class UserServiceImpl implements UserService{
private UserDao userDao=new UserDaoImpl();
@Override
public int addUser(User user) throws Exception {
return userDao.addUser(user);
}
@Override
public User login(String uname,String psw) throws Exception {
return userDao.login(uname,psw);
}
@Override
public List<User> getUserList() throws Exception {
return userDao.getUserList();
}
@Override
public User byID(int i) throws Exception {
return userDao.byID(i);
}
@Override
public int updateUser(User user) throws Exception{
return userDao.updateUser(user);
}
@Override
public int deleteUser(int i) throws Exception {
return userDao.deleteUser(i);
}
}
package com.mine.Test;
import com.mine.dao.UserDao;
import com.mine.dao.UserDaoImpl;
import com.mine.pojo.User;
import org.junit.Test;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
public class Demo {
UserDao userDao=new UserDaoImpl();
public static void main(String[] args) {
}
@Test
public void fun1() throws Exception{
User user=new User();
user.setId(2);
user.setUsername("superman");
user.setPassword("888");
user.setAddress("cq");
user.setState(1);
//将时间转换为字符串
Date date=new Date();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String s=sdf.format(date);
user.setTime(s);
UserDao userDao=new UserDaoImpl();
int i = userDao.addUser(user);
System.out.println(i);
}
@Test
public void fun2() throws Exception{
List<User> userList=userDao.getUserList();
for (User user : userList) {
System.out.println(user);
}
}
@Test
public void fun3() throws Exception{
User user=new User();
user.setId(3);
user.setUsername("qwer");
user.setPassword("qewr");
user.setAddress("TY");
user.setState(1);
Date date=new Date();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String s = sdf.format(date);
user.setTime(s);
int i = userDao.updateUser(user);
if (i>0){
System.out.println("ok!!");
}else {
System.out.println("fuck!");
}
}
}
package com.mine.pojo;
public class User {
private int id;
private String username;
private String password;
private String address;
private int state;
private String time;
public User() {
}
public User(int id, String username, String password, String address, int state, String time) {
this.id = id;
this.username = username;
this.password = password;
this.address = address;
this.state = state;
this.time = time;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", address='" + address + '\'' +
", state='" + state + '\'' +
", time='" + time + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public int getState() {
return state;
}
public void setState(int state) {
this.state = state;
}
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
}
package com.mine.dao;
import com.mine.Test.Demo;
import com.mine.pojo.User;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* JDBC工具类封装
*
* @author Joker
* @version 1.0
*
*/
public class JDBCTools {
//驱动类的名称
//使用此方法可以随意修改所连接的数据库,使数据库信息装在properties文件中
private static Properties p=new Properties();
static {
try {
ClassLoader classLoader= Demo.class.getClassLoader();
InputStream inputStream =classLoader.getResourceAsStream("db.properties");
try {
p.load(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
Class.forName(p.getProperty("driverClassName"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 该方法返回JDBC connection 对象 封装
*
* @return
*/
public static Connection getConnection() throws Exception {
Connection con=null;
try {
con=DriverManager.getConnection(p.getProperty("url"),p.getProperty("user"),p.getProperty("password"));
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
/**
* 对查询sql语句进行封装
*
* @return
*/
public static ResultSet MyExecuteQuery(String sql,Connection connection) throws Exception{
Statement statement=connection.createStatement();
return statement.executeQuery(sql);
}
/**
* 执行 增加、删除、修改操作 封装
*
*/
public static int MyExecuteUpdate(String sql,Connection connection) throws Exception{
Statement statement=connection.createStatement();
return statement.executeUpdate(sql);
}
/**
* 执行 预编译模板方式 增加、删除、修改操作 封装
*
*/
public static int MyExecutePrepareUpdate(String sql, Connection connection, User user) throws Exception{
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,user.getId());
preparedStatement.setString(2,user.getUsername());
preparedStatement.setString(3,user.getPassword());
preparedStatement.setString(4,user.getAddress());
preparedStatement.setInt(5,user.getState());
preparedStatement.setString(6,user.getTime());
return preparedStatement.executeUpdate();
}
/**
* 释放资源封装
*
*/
public static void close(ResultSet rs, Statement sm,Connection cn) {
if (rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (sm!=null){
try {
sm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (cn!=null){
try {
cn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
学习过程11(三层思想的增删改查)
最新推荐文章于 2023-03-02 19:05:18 发布