这是表中的数据和字段中的约束
- 实现登录和注册功能的代码如下:
package jdbc.homeWork;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class Login {
//用户的注册,首先先进行判断该用户是否存在,如果存在就登陆,否则注册
static int id2;
static String userName2;
static String pwd2;
static String email2;
public static void main(String[] args) throws Exception {
//输入表中各个字段的数据字段
Scanner sca=new Scanner(System.in);
System.out.println("输入id");
id2=sca.nextInt();
System.out.println("输入name");
userName2=sca.next();
System.out.println("输入密码");
pwd2=sca.next ();
System.out.println("输入电子邮箱");
email2=sca.next();
String sql_primary_key="select *from t_user where id="+"'"+id2+"'";
String sql="select *from t_user where userName="+"'"+userName2+"'";
String sql2="insert into t_user values(?,?,?,?)";
help h=new help();
ResultSet rs=h.res(sql);
ResultSet rsId=h.res(sql_primary_key);
//这里不能用while的原因是要执行else
// while(rs.next()) {
// System.out.println("该用户已经存在");
// }
//这样的话就没法写else;
if(rsId.next()) {
throw new Exception("该表的主键已存在,重新更换id值");
}
else if(rs.next()){
throw new Exception("该用户已经存在,重更换用户名");
}
else {
PreparedStatement ps2=h.JD(sql2);
ps2.setInt(1, id2);
ps2.setString(2, userName2);
ps2.setString(3, pwd2);
ps2.setString(4, email2);
ps2.execute();
System.out.println("注册成功");
ps2.close();
}
}
}
class help{
public ResultSet res(String sql) throws SQLException {
Connection conn=JdbcUtil.getConnection();
PreparedStatement ps=conn.prepareStatement(sql);
ResultSet rs1=ps.executeQuery();
return rs1;
}
public PreparedStatement JD(String sql) throws SQLException {
Connection conn=JdbcUtil.getConnection();
PreparedStatement ps=conn.prepareStatement(sql);
return ps;
}
}
String sql=“select *from t_user where userName=”+"’"+userName2+"’";
注意这条sql语句的书写
- 用户对数据库进行删除操作,但要先判断该用户是否存在
1.代码如下:
public static void delete_t_user(String name) throws SQLException {
String sql_name="select *from t_user where userName="+"'"+name+"'";
String delete_sql="delete from t_user where userName=?";
help h=new help();
ResultSet rs=h.res(sql_name);
if(rs.next()) {
System.out.println("delete");
PreparedStatement ps=h.JD(delete_sql);
ps.setString(1, name);
ps.execute();
System.out.println("删除成功");
}
else {
System.out.println("no delete");
}
}
3 对数据库进行查询操作
- 分别按用户的名字和全部查询:
2.代码如下:
public static void select_t_user(String select_name) throws SQLException {
//String select_sql="select *from t_user where userName="+"'"+select_name+"'";
String sql_selectname="select *from t_user where userName="+"'"+select_name+"'";
String select_one="select *from t_user where userName=?";
help h=new help();
ResultSet rs=h.res(sql_selectname);
if(rs.next()) {
PreparedStatement ps=h.JD(select_one);
ps.setString(1, select_name);
ResultSet res=ps.executeQuery();
while(res.next()) {
System.out.println(res.getInt(1)+"\t"+res.getString(2)+"\t"+res.getString(3)+"\t"+res.getString(4));
}
}
}
public static void selectAll() throws SQLException {
String select_All="select *from t_user";
help h=new help();
PreparedStatement ps=h.JD(select_All);
ResultSet res=ps.executeQuery();
while(res.next()) {
System.out.println(res.getInt(1)+"\t"+res.getString(2)+"\t"+res.getString(3)+"\t"+res.getString(4));
}
}