Java–JDBC经典练习题
T_User表:
字段名称–说明–数据类型–约束 备注
id --编号 --int–主键
userName–用户名 --varchar(50)-- 唯一、不允许空
pwd --密码–varchar(50) --不允许空
email–邮箱–varchar(50)-- 唯一、不允许为空
数据示例:
id UserName Pwd Email
2 Jerry 888888 Jerry@126.com
1.实现用户的注册功能
2.实现用户的登陆功能
3.实现用户的修改，但要考虑用户是否真实存在
4.实现用户的删除功能。
5.实现用户的查询功能，查询全部及根据userName名去查询
6.通过Scanner实现人机交互。
流程如下，先显示菜单，提示用户选择登录还是注册，选择注册时，让用户输入用户名以及密码、邮箱，输入用户名时判断此用户是否存在，如果存在则不能完成注册，不存在则注册成功；选择登录之后输入用户名以及密码判断用户名是否存在，不存在给出提示，存在的话判断密码是否正确，如果密码正确则进行登录，执行对用户的查询、删除、修改操作
package com.rang.excercise;
public class User {
private int id;
private String username;
private String pwd;
private String email;
public User() {
}
public User(int id, String username, String pwd, String email) {
this.id = id;
this.username = username;
this.pwd = pwd;
this.email = email;
}
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 getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "编号："+this.getId()+",姓名："+this.getUsername()+",邮件："+this.getEmail();
}
}
package com.rang.excercise;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class UserDao implements BaseDao<User> {
public UserDao() {
}
@Override
public void insert(User t) {
String sql = "insert into t_user(username,pwd,email) values(?,?,?);";
DbTools.executeUpdate(sql, t.getUsername(),t.getPwd(),t.getEmail());
}
@Override
public void update(User t) {
String sql = "update t_user set username=?,pwd=?,email=? where id=?";
DbTools.executeUpdate(sql, t.getUsername(),t.getPwd(),t.getEmail(),t.getId());
}
@Override
public void delete(User t) {
String sql = "delete from t_user where id=?";
DbTools.executeUpdate(sql, t.getId());
}
@Override
public User selectById(User t) {
User user = null;
String sql = "select * from t_user where id=?";
List<Object[]> list = DbTools.executeQuery(sql, t.getId());
if (list.size() > 0) {
Object[] objs = list.get(0);
int id = (Integer) objs[0];
String username = (String) objs[1];
String pwd = (String) objs[2];
String email=(String) objs[3];
user =new User(id,username,pwd,email);
}
return user;
}
@Override
public List<User> selectAll() {
List<User> users = new ArrayList<>();
String sql = "select * from t_user";
List<Object[]> list=DbTools.executeQuery(sql,null);
if (list.size() > 0) {
for(int i=0;i<list.size();i++){
Object[] objs = list.get(i);
int id = (Integer) objs[0];
String username = (String) objs[1];
String pwd = (String) objs[2];
String email=(String) objs[3];
User user =new User(id,username,pwd,email);
users.add(user);
}
}
return users;
}
public User selectUserByUserName(String key){
User user = null;
String sql = "select * from t_user where username=? ";
List<Object[]> list = DbTools.executeQuery(sql, key);
if (list.size() > 0) {
Object[] objs = list.get(0);
int id = (Integer) objs[0];
String username = (String) objs[1];
String pwd = (String) objs[2];
String email=(String) objs[3];
user =new User(id,username,pwd,email);
}
return user;
}
public User selectUserByEmail(String key){
User user = null;
String sql = "select * from t_user where email=? ";
List<Object[]> list = DbTools.executeQuery(sql, key);
if (list.size() > 0) {
Object[] objs = list.get(0);
int id = (Integer) objs[0];
String username = (String) objs[1];
String pwd = (String) objs[2];
String email=(String) objs[3];
user =new User(id,username,pwd,email);
}
return user;
}
}
package com.rang.excercise;
import java.util.List;
public interface BaseDao<T> {
public abstract void insert(T t);
public abstract void update(T t);
public abstract void delete(T t);
public abstract T selectById(T t);
public abstract List<T> selectAll();
}
package com.rang.excercise;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class DbTools {
//URL
private static final String URL="jdbc:mysql://localhost:3306/test";
//user
private static final String USER="root";
//pwd
private static final String PWD="root";
//PreparedStatement 对象
private static PreparedStatement preparedStatement=null;
//ResultSet对象
private static ResultSet resultSet=null;
//connection对象
private static Connection connection=null;
//编写一个静态块用于加载驱动
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
if (connection == null) {
try {
connection = DriverManager.getConnection(URL, USER, PWD);
connection.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
}
return connection;
}
//insert,update,delete
public static void executeUpdate(String sql,Object...objs){
//获取connection 对象
connection=getConnection();
//获取PreparedStatement 对象
try {
preparedStatement=connection.prepareStatement(sql);
if(objs!=null){
for(int i=0;i<objs.length;i++){
preparedStatement.setObject(i+1,objs[i]);
}
}
preparedStatement.executeUpdate();
commit();
} catch (SQLException e) {
e.printStackTrace();
rollback();
}finally {
close();
}
}
//select方法
public static List<Object[]> executeQuery(String sql,Object...objs){
List<Object[]> list=new ArrayList<>();
connection=getConnection();
try {
preparedStatement=connection.prepareStatement(sql);
if(objs!=null&&objs.length>0){
for(int i=0;i<objs.length;i++){
preparedStatement.setObject(i+1,objs[i]);
}
}
resultSet=preparedStatement.executeQuery();
while(resultSet.next()){
Object[] datas=new Object[resultSet.getMetaData().getColumnCount()];
for(int i=0;i<datas.length;i++){
datas[i]=resultSet.getObject(i+1);
}
list.add(datas);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
close();
}
return list;
}
//事务提交
public static void commit(){
if (connection!=null){
try {
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//回滚
public static void rollback(){
if (connection!=null){
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//静态方法关闭资源
public static void close(){
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(preparedStatement !=null){
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
connection=null;
}
}
}
package com.rang.excercise;
public class Menu {
public static void showMenu(){
System.out.println("欢迎登录XXX系统");
System.out.println("1.注册");
System.out.println("2.登录");
System.out.println("3.退出");
}
public static void showOperator(){
System.out.println("请选择你要执行的操作：");
System.out.println("1.修改当前用户");
System.out.println("2.注销当前用户");
System.out.println("3.查询用户");
}
public static void showSelect(){
System.out.println("1.按名称查询");
System.out.println("2.查询全部");
}
}
package com.rang.excercise;
import java.util.Scanner;
public class Input {
private Scanner scan=null;
private UserDao userDao=null;
public Input(UserDao userDao){
scan=new Scanner(System.in);
this.userDao=userDao;
}
public String getString(String info){
System.out.println(info);
return scan.nextLine();
}
public int getInt(String info,String error){
String str=getString(info);
while(!str.matches("\\d+")){
System.out.println(error);
str=getString(info);
}
return Integer.parseInt(str);
}
public String getEmail(String info,String error){
String str=getString(info);
while(!str.matches("\\w{5,20}@\\w{5,7}(\\.com|\\.cn|\\.net)")){
System.out.println(error);
str=getString(info);
}
User user=userDao.selectUserByEmail(str);
if(user!=null){
str="";
System.out.println("电子邮件已被占用了！");
}
return str;
}
public String getUserName(String info){
String key=getString(info);
User user=userDao.selectUserByUserName(key);
if(user!=null){
System.out.println("用户已存在！！");
key="";
}
return key;
}
}
package com.rang.excercise;
import java.util.List;
public class Operator {
private boolean flag;
private Input in = null;
private UserDao userDao = new UserDao();
public Operator() {
flag = true;
in = new Input(userDao);
}
public void operator() {
while (flag) {
Menu.showMenu();
int n = in.getInt("请选择您要执行的操作", "选择错误，重新选择！");
switch (n) {
case 1:
String username = in.getUserName("请输入用户名：");
if (username.equals("")) {
break;
}
String pwd = in.getString("请输入密码：");
String email = in.getEmail("请输入电子邮件：", "格式不正确！");
if (email.equals("")) {
break;
}
User user = new User(0, username, pwd, email);
userDao.insert(user);
break;
case 2:
//获取用户输入的用户名
String username1 = in.getString("请输入用户名:");
User user1 = userDao.selectUserByUserName(username1);
if (user1 == null) {
System.out.println("用户名不存在");
break;
}
String pwd1 = in.getString("请输入密码:");
if (!pwd1.equals(user1.getPwd())) {
System.out.println("密码输入错误！");
break;
}
Menu.showOperator();
//获取用户输入的信息
int n1 = in.getInt("请选择您要执行的操作：", "选项应该是整数数字");
switch (n1) {
case 1:
//获取用户输入的用户名
String username2 = in.getUserName("请输入用户名:");
if (username2.equals("")) {
username2 = user1.getUsername();
}
//获取用户输入的密码
String pwd2 = in.getString("请输入密码:");
//获取用户输入的电子邮件
String email2 = in.getEmail("请输入电子邮件:", "电子邮件的格式不正确");
if (email2.equals("")) {
email2 = user1.getEmail();
}
user1.setEmail(email2);
user1.setPwd(pwd2);
user1.setUsername(username2);
userDao.update(user1);
break;
case 2:
userDao.delete(user1);
break;
case 3:
Menu.showSelect();
int n2 = in.getInt("请选择您要执行的操作：", "选项应该是整数数字");
switch (n2) {
case 1:
String username3 = in.getString("请输入用户名:");
User user2 = userDao.selectUserByUserName(username3);
if (user2 == null) {
System.out.println("没有此用户");
} else {
System.out.println(user2);
}
break;
case 2:
List<User> users = userDao.selectAll();
for (User u : users) {
System.out.println(u);
}
break;
default:
System.out.println("没有这个选项");
break;
}
break;
default:
System.out.println("没有这个选项");
break;
}
break;
case 3:
System.out.println("系统退出成功！");
flag = false;
break;
default:
System.out.println("没有此选项");
break;
}
}
}
}
1282
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
