项目文件结构
项目运行效果
项目详细代码
JDBCUtils.java
package jdbc;
import java.sql.*;
import com.mysql.jdbc.Driver;
public class JDBCUtils {
private static final String connectionURL="jdbc:mysql://localhost:3306/books?useSSL=false&useUnicode=true&characterEncoding=UTF8";
private static final String username="root";
private static final String password="1234";
public static Connection getConnection()
{
try {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager.getConnection(connectionURL,username, password);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void close(ResultSet rs,Statement stmt,Connection conn)
{
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(stmt!=null)
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(conn!=null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Register.java
package jdbc;
import java.util.Scanner;
public class Register {
String[] Username = new String[20];
String[] Password = new String[20];
public void loginfirst(){
Scanner input=new Scanner(System.in);
int num=0;
boolean sign = false;
do{
System.out.println(" 温馨提示：请先注册！！！ ");
System.out.println(" 1.注册帐号 ");
System.out.println(" 2.登录系统 ");
System.out.println(" 3.退出系统 ");
String a = input.next();
switch(a){
case "1":
do{
System.out.println("请输入想要注册的用户名");
Username[num] = input.next();
System.out.println("请输入想要注册的密码");
String passwordfirst = input.next();
System.out.println("请再次输入密码");
String passwordsecond = input.next();
if(passwordfirst.equals(passwordsecond)){
Password[num] = passwordfirst;
System.out.println("新用户："+Username[num]+"创建成功");
sign = true;
}else{
System.out.println("两次输入的密码不一致");
sign = false;
}
}while(!sign);
num++;
continue;
case "2":
sign = false;
break;
case "3":
System.exit(-1);
}
}while(sign);
}
public void loginsecond(){
boolean flag=true;
Scanner in=new Scanner(System.in);
do{
System.out.println("请输入登陆账号");
String username = in.next();
System.out.println("请输入密码");
String password = in.next();
for(int i=0;i<Username.length;i++){
if(username.equals(Username[i])&&password.equals(Password[i])){
System.out.println("登陆中");
try {
Thread.sleep(1500);
} catch (InterruptedException e) {
e.printStackTrace();
}
System.out.println("登陆成功");
flag= false;
break;
}
}
if(flag==true){
System.out.println("账号或者密码错误，请重新登录");
flag = true;
}
}while(flag);
}
}
Test.java
package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class Test {
public static void main(String[] args) {
}
public static void selectAll()
{
Connection con=null;
Statement stmt=null;
ResultSet rs=null;
try {
con=JDBCUtils.getConnection();
stmt=con.createStatement();
rs=stmt.executeQuery("select * from book,bookmagic where book.bno=bookmagic.bno");
System.out.println(" 编号 书籍名 主人 页码 价格 作者 ");
while(rs.next())
{
int bno=rs.getInt("bno");
String name=rs.getString("name");
String owner=rs.getString("owner");
int page=rs.getInt("page");
int price=rs.getInt("price");
String article=rs.getString("article");
System.out.println(" "+bno+" "+name+" "+owner+" "+page+" "+price+" "+ article);
System.out.println(" ");
}
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtils.close(rs, stmt, con);
}
}
public static void selectUserByPage(int pageNumber,int pageCount)
{
Connection con=null;
PreparedStatement stmt=null;
ResultSet rs=null;
try {
con=JDBCUtils.getConnection();
stmt=con.prepareStatement("select* from book limit ?,?");
//mysql中 使用limit查询的参数第一个为 起始记录数; 第二个为 每页的数量
stmt.setInt(1, (pageNumber-1)*pageCount);
stmt.setInt(2, pageCount);
rs=stmt.executeQuery();
while(rs.next())
{
int bno=rs.getInt("bno");
String name=rs.getString("name");
String owner=rs.getString("owner");
int page=rs.getInt("page");
int price=rs.getInt("price");
System.out.println("-----------------------------");
System.out.println(" 编号 书籍名 主人 页码 价格 ");
System.out.println(" "+bno+" "+name+" "+owner+" "+page+" "+price);
System.out.println("-----------------------------");
}
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtils.close(rs, stmt, con);
}
}
public static void insert(int number,String passname,String own,int pages,int prices)
{
Connection con=null;
PreparedStatement stmt=null;
ResultSet rs=null;
try {
con=JDBCUtils.getConnection();
String sql="insert into book values(?,?,?,?,?)";
stmt=con.prepareStatement(sql);
stmt.setInt(1, number);;
stmt.setString(2, passname);
stmt.setString(3, own);
stmt.setInt(4, pages);
stmt.setInt(5, prices);
int result =stmt.executeUpdate();//返回值代表收到影响的行列
} catch (Exception e) {
e.printStackTrace();
} finally{
System.out.println("添加成功！！");
JDBCUtils.close(rs, stmt, con);
}
}
public static void delete (int price)
{
Connection con=null;
PreparedStatement stmt=null;
ResultSet rs=null;
try {
con=JDBCUtils.getConnection();
String sql="delete from book where price= ?";
stmt=con.prepareStatement(sql);
stmt.setInt(1, price);;
int result =stmt.executeUpdate();//返回值代表收到影响的行列
if(result>0)
{
System.out.println("删除成功！");
}else{
System.out.println("删除失败！");
}
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtils.close(rs, stmt, con);
}
}
public static void update(int number,String passname,String own,int pages,int prices){
Connection con=null;
PreparedStatement stmt=null;
ResultSet rs=null;
try {
con=JDBCUtils.getConnection();
String sql="update book set price=?,name=?,owner=?,page=? where bno=? ";
stmt=con.prepareStatement(sql);
stmt.setInt(1, prices);
stmt.setString(2, passname);
stmt.setString(3, own);
stmt.setInt(4, pages);
stmt.setInt(5, number);
int result =stmt.executeUpdate();//返回值代表收到影响的行列
if(result>0)
{
System.out.println("更新成功！");
}else{
System.out.println("更新失败！");
}
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtils.close(rs, stmt, con);
}
}
}
CURD.java
package jdbc;
import java.util.Scanner;
public class CURD {
public static void main(String[] args) {
Register t=new Register();
t.loginfirst();
t.loginsecond();
Test te=new Test();
Scanner input = new Scanner(System.in);
while(true)
{
menu();
int a = input.nextInt();
switch(a) {
case 1: te.selectAll();break;
case 2:
System.out.println("请输入起始记录数：");
int pageNumber = input.nextInt();
System.out.println("每页的数量");
int pageCount= input.nextInt();
te.selectUserByPage(pageNumber,pageCount); break;
case 3:
System.out.println("请输入添加的书籍编号：");
int number = input.nextInt();
System.out.println("请输入添加的书籍名字：");
String passname = input.next();
System.out.println("请输入添加的书籍主人：");
String own = input.next();
System.out.println("请输入添加的书籍页码：");
int pages = input.nextInt();
System.out.println("请输入添加的书籍价格：");
int prices = input.nextInt();
te.insert(number,passname,own,pages,prices); break;
case 4:
System.out.println("请输入你要删除的书籍的对应的价格：");
int prices1=input.nextInt();
te.delete(prices1);break;
case 5:
System.out.println("请根据需求进行书籍信息的修改：");
System.out.println(" ");
System.out.println("你要更新书籍编号：");
int number2 = input.nextInt();
System.out.println("你要更新书籍名字：");
String passname2 = input.next();
System.out.println("你要更新书籍主人：");
String own2 = input.next();
System.out.println("你要更新书籍页码：");
int pages2 = input.nextInt();
System.out.println("你要更新书籍的价格：");
int prices3 = input.nextInt();
te.update(number2,passname2,own2,pages2,prices3);break;
case 6 :
System.out.println("操作结束");
System.exit(-1);
}
}
}
public static void menu() {
System.out.println(" ---------- ∮ 书籍管理系统 ∮ -------------");
System.out.println(" ");
System.out.println(" 》 1. 查询所有书籍信息 ");
System.out.println(" 》 2. 分页查询书籍信息 ");
System.out.println(" 》 3. 增加书籍信息 ");
System.out.println(" 》 4. 删除书籍信息 ");
System.out.println(" 》 5. 修改书籍信息 ");
System.out.println("-----------------------------------------");
System.out.println(" 》 6. 操作结束 ");
System.out.println(" ∮ 请输入你要输入的编号 : ");
}
}
项目文件下载
相关案例
案例一
package hello;
import java.sql.*;
import java.awt.AWTException;
import java.awt.Robot;
import java.awt.event.InputEvent;
import java.awt.event.KeyEvent;
import java.util.Scanner;
public class 实验 {
static void clear() throws AWTException //清屏
{
Robot r = new Robot();
r.mousePress(InputEvent.BUTTON3_MASK); // 按下鼠标右键
r.mouseRelease(InputEvent.BUTTON3_MASK); // 释放鼠标右键
r.keyPress(KeyEvent.VK_CONTROL); // 按下Ctrl键
r.keyPress(KeyEvent.VK_R); // 按下R键
r.keyRelease(KeyEvent.VK_R); // 释放R键
r.keyRelease(KeyEvent.VK_CONTROL); // 释放Ctrl键
r.delay(100);
}
static void jumplong() throws InterruptedException //跳转暂停
{
System.out.println("五秒后自动跳转");
Thread.sleep(5000);
}
static void jumpshort() throws InterruptedException //跳转暂停
{
System.out.println("两秒后自动跳转");
Thread.sleep(2000);
}
public static void add() throws Exception //增加学生信息
{
Connection con = null;
PreparedStatement ps=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sys?&useSSL=false&serverTimezone=UTC","root","zck12121");
Scanner a=new Scanner(System.in);
while(true)
{
System.out.print("输入学生姓名：");
String name=a.next();
System.out.print("输入学生性别：");
String sex=a.next();
System.out.print("输入学生地址：");
String site=a.next();
System.out.print("输入学生专业：");
String major=a.next();
System.out.print("输入学生专业成绩：");
Float maiorgrade=a.nextFloat();
String sql="insert into student(name,sex,site,major,majorgrade)values(?,?,?,?,?)";
ps = con.prepareStatement(sql);
ps.setString(1,name);
ps.setString(2,sex);
ps.setString(3,site);
ps.setString(4,major);
ps.setFloat(5,maiorgrade);
ps.execute();
System.out.println("是否继续输入学生信息\t请输入yes/no");
String no="no";
String choose=a.next();
clear();
if(no.equals(choose))
break;
}
} catch (Exception e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}finally {
ps.close();
con.close();
}
}
public static void alldata() throws Exception
{
Connection con = null;
Statement sta = null;
ResultSet rs = null;
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sys?&useSSL=false&serverTimezone=UTC","root","zck12121");
sta = con.createStatement();
String sql="select * from student";
rs=sta.executeQuery(sql);//执行sql语句
System.out.println("学生姓名\t学生性别\t学生地址\t学生专业\t学生专业成绩");
while(rs.next()){
System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getString(5));
}
}
public static void delete() throws Exception //删除
{
Connection con = null;
Statement sta = null;
ResultSet rs = null;
PreparedStatement ps=null;
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sys?&useSSL=false&serverTimezone=UTC","root","zck12121");
boolean n=true;
int y=0;
while(n)
{
System.out.print("请输入要删除的学生姓名：");
Scanner a=new Scanner(System.in);
String name=a.next();
sta = con.createStatement();
String sql="select * from student",Sql;
rs=sta.executeQuery(sql);//执行sql语句
while(rs.next()){
int m=name.compareTo(rs.getString(1));
if(m==0)
{
y=1;
}
}
if(y==0)
{
System.out.println("该用户名不存在，请重新输入");
continue;
}
Sql="delete from student where name = ? ";
ps = con.prepareStatement(Sql);
ps.setObject(1, name);
ps.execute();
n=false;
System.out.println("删除成功！");
}
}
public static void updata() throws Exception //更新
{
Connection con = null;
Statement sta = null;
ResultSet rs = null;
PreparedStatement ps=null;
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sys?&useSSL=false&serverTimezone=UTC","root","zck12121");
boolean n=true;
int y=0;
while(n)
{
System.out.print("请输入要更改的学生姓名：");
Scanner a=new Scanner(System.in);
String name=a.next();
sta = con.createStatement();
String sql="select * from student";
rs=sta.executeQuery(sql);//执行sql语句
while(rs.next()){
int m=name.compareTo(rs.getString(1));
if(m==0)
{
y=1;
}
}
if(y==0)
{
System.out.println("该用户名不存在，请重新输入");
continue;
}
System.out.println("您要修改该学生的");
System.out.println("1.姓名");
System.out.println("2.性别");
System.out.println("3.地址");
System.out.println("4.专业");
System.out.println("5.专业成绩");
int choose=a.nextInt();
String Sql;
if(choose==1)
{
System.out.print("将姓名修改为：");
String news=a.next();
Sql="update student 9 as 9 set name = ? where name = ? ";
ps = con.prepareStatement(Sql);
ps.setObject(1, news);
ps.setObject(2, name);
ps.execute();
n=false;
System.out.println("修改成功！");
}
else if(choose==2)
{
System.out.print("将性别修改为：");
String news=a.next();
Sql="update student set sex=? where name=?";
ps = con.prepareStatement(Sql);
ps.setObject(1, news);
ps.setObject(2, name);
ps.execute();
n=false;
System.out.println("修改成功！");
}
else if(choose==3)
{
System.out.print("将地址修改为：");
String news=a.next();
Sql="update student set site=? where name=?";
ps = con.prepareStatement(Sql);
ps.setObject(1, news);
ps.setObject(2, name);
ps.execute();
n=false;
System.out.println("修改成功！");
}
else if(choose==4)
{
System.out.print("将专业修改为：");
String news=a.next();
Sql="update student set major=? where name=?";
ps = con.prepareStatement(Sql);
ps.setObject(1, news);
ps.setObject(2, name);
ps.execute();
n=false;
System.out.println("修改成功！");
}
else if(choose==5)
{
System.out.print("将专业成绩修改为：");
float news=a.nextFloat();
Sql="update student set majorgrade=? where name=?";
ps = con.prepareStatement(Sql);
ps.setObject(1, news);
ps.setObject(2, name);
ps.execute();
n=false;
System.out.println("修改成功！");
}
}
}
public static void group() throws Exception //降序查询成绩
{
Connection con = null;
Statement sta = null;
ResultSet rs = null;
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sys?&useSSL=false&serverTimezone=UTC","root","zck12121");
sta = con.createStatement();
System.out.println("1.查看全部学生信息");
System.out.println("2.查看前三名学生信息");
String Sql=null;
Scanner a=new Scanner(System.in);
int choose=a.nextInt();
if(choose==1)
Sql="select * from student ORDER BY majorgrade DESC";
if(choose==2)
Sql="select * from student order by majorgrade asc limit 0,3";
rs=sta.executeQuery(Sql);//执行sql语句
System.out.println("学生姓名\t学生性别\t学生专业成绩");
while(rs.next()){
System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(5));
}
}
public static void go() throws Exception //进入系统
{
while(true)
{
clear();
System.out.println("1.增加学生信息");
System.out.println("2.删除学生信息");
System.out.println("3.修改学生信息");
System.out.println("4.学生成绩表");
System.out.println("0.查看学生信息");
System.out.println("5.退出程序");
int choose;
Scanner a=new Scanner(System.in);
clear();
switch(choose=a.nextInt())
{
case 1:add();break;
case 2:delete();break;
case 3:updata();break;
case 4:group();break;
case 0:alldata();break;
case 5:System.exit(-1);
default:System.out.println("未找到该功能，请重新输入");break;
}
jumplong();
}
}
public static int get(String username,String password) throws Exception//账号密码核对
{
Connection con = null;
Statement sta = null;
ResultSet rs = null;
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sys?&useSSL=false&serverTimezone=UTC","root","zck12121");
sta = con.createStatement();
String sql="select * from register";
rs=sta.executeQuery(sql);//执行sql语句
while(rs.next()){
int a=username.compareTo(rs.getString(1));
int b=password.compareTo(rs.getString(2));
if(a==0&&b==0)
return 1;
else if(a==0&&b!=0)
return 2;
}
return 0;
}
public static void enroll() throws Exception //登录
{
Scanner s=new Scanner(System.in);
System.out.print("请输入账号：");
String user=s.next();
System.out.print("请输入密码：");
String word=s.next();
int w=get(user,word);
if(w==1)
{
System.out.println("登录成功！");
jumpshort();
go();
}
else if(w==2)
System.out.println("密码有误，请重新输入");
else
System.out.println("该用户不存在");
}
public static boolean check1(String name1) throws Exception //用户姓名查重
{
Connection con = null;
Statement sta = null;
ResultSet rs = null;
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sys?&useSSL=false&serverTimezone=UTC","root","zck12121");
sta = con.createStatement();
String sql="select * from register";
rs=sta.executeQuery(sql);//执行sql语句
while(rs.next()){
int a=name1.compareTo(rs.getString(1));
if(a==0)
{
System.out.println("该用户名已存在，请重新输入");
return true;
}
}
return false;
}
public static void logon() throws Exception //注册
{
Connection con = null;
Statement sta = null;
PreparedStatement ps=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sys?&useSSL=false&serverTimezone=UTC","root","zck12121");
Scanner a=new Scanner(System.in);
boolean n=true;
String newname = null;
while(n)
{
System.out.print("请输入您的ID：");
newname=a.next();
n=check1(newname);
}
System.out.print("请输入您的密码：");
String newword=a.next();
String sql="insert into register(username,password)values(?,?)";
ps = con.prepareStatement(sql);
ps.setString(1,newname);
ps.setString(2,newword);
ps.execute();
} catch (Exception e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}finally {
ps.close();
con.close();
}
System.out.println("注册成功！");
}
public static void welcome() throws Exception //初始界面
{
System.out.println("欢迎进入学生管理系统");
jumplong();
clear();
while(true)
{
System.out.println("1.登录");
System.out.println("2.注册");
Scanner a=new Scanner(System.in);
int choose=a.nextInt();
if(choose==1)
enroll();
else if(choose==2)
logon();
else
System.out.println("没有该选项，请重新输入");
jumplong();
clear();
}
}
public static void main(String[] args) throws Exception{
welcome();
}
}
案例二
package Demo04;
import java.sql.*;
import java.util.*;
public class mainconnection {
public static void main(String[] args) {
System.out.println(" ---------------------");
System.out.println(" --数据库连接成功！！--");
System.out.println(" ---------------------");
menu();
}
//数据库连接
public static Connection TheSqlconnection() {
Connection con=null;
try {
//加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//连接MySQL
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo02?serverTimezone=GMT","root","748294");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
//登录
private static void register() {
Scanner sc = new Scanner(System.in);
System.out.println("******************************");
System.out.println("* 1.注册 *");
System.out.println("* 2.登录 *");
System.out.println("* 0.返回 *");
System.out.println("******************************");
System.out.println(" ");
System.out.println("请输入你的选择： ");
int num=sc.nextInt();
sc.nextLine();
if(num==1)
{
System.out.println("请输入用户名： ");
String a=sc.nextLine();
System.out.println("请输入密码： ");
String b=sc.nextLine();
System.out.println("注册成功！！！");
System.out.println("---------------");
while(true)
{
System.out.println("**********************");
System.out.println("* 1.登录 *");
System.out.println("* 0.返回 *");
System.out.println("**********************");
System.out.println("请输入你的选择： ");
int x=sc.nextInt();
sc.nextLine();
if(x==1)
{
System.out.println("请输入用户名： ");
String c=sc.nextLine();
if(a.equals(c))
{
System.out.println("请输入密码： ");
String d=sc.nextLine();
if(b.equals(d))
{
System.out.println("密码正确！！！");
}else{
System.out.println("密码错误！！！");
System.out.println("请重新输入密码： ");
d=sc.nextLine();
if(b.equals(d))
{
System.out.println("密码正确！！！");
}else{
System.out.println("密码再次错误 即将离开该页面！！！");
System.exit(0);
}
}
}else{
System.out.println("用户名输入错误！！ 已离开该页面");
System.out.println("---------------");
menu();
}
}if(x==0)
{
System.out.println("成功返回！！");
System.out.println("---------------");
menu();
}
break;
}
}else if(num==2){
System.out.println("尚未注册 无法登录！！！");
System.out.println("如想登陆 请前往注册！！！");
System.out.println("---------------");
register();
}else if(num==0){
System.out.println("成功返回！！！");
System.out.println("---------------");
menu();
}
return ;
}
//主菜单
public static void menu() {
TheSqlconnection();
Scanner sm=new Scanner(System.in);
System.out.println("*************************************");
System.out.println("-- 学生管理系统 --");
System.out.println("************************************");
System.out.println(" 1.管理员 ");
System.out.println(" 2.学生 ");
System.out.println(" 0.退出 ");
System.out.println("*************************************");
System.out.println("请输入你的选择");
int m=sm.nextInt();
if(m==1) {
menu1();
}else if(m==2) {
menu2();
}else if(m==0) {
System.out.println("------------------");
System.out.println("--已退出 请勿操作--");
System.out.println("------------------");
System.exit(0);
}else {
System.out.println("----输入错误 将重复此操作：");
menu();
}
}
//管理员菜单
public static void menu1() {
register();
for(int i=0;i<1000;i++) {
Scanner sc=new Scanner(System.in);
System.out.println(" **************************************");
System.out.println(" -- 管理员 你好 ");
System.out.println(" **************************************");
System.out.println("*** 1.增加学生信息 ***");
System.out.println("*** 2.删除学生信息 ***");
System.out.println("*** 3.查询学生信息 ***");
System.out.println("*** 4.更新学生信息 ***");
System.out.println("*** 5.分页查询学生信息 ***");
System.out.println("*** 6.分组排序学生信息 ***");
System.out.println("*** 7.显示学生信息 ***");
System.out.println("*** 0.返回 ***");
System.out.println("*****************************************");
System.out.println("请输入你的选择");
int x=sc.nextInt();
switch(x) {
case 1:add();
break;
case 2:del();
break;
case 3:search();
break;
case 4:update();
break;
case 5:
pages();
break;
case 6:order();
break;
case 7:print();
break;
case 0:menu();
break;
default:
System.out.println("---输入错误 请重新输入！！！");
menu1();
break;
}
}
}
//学生菜单
public static void menu2() {
for(int b=0;b<500;b++) {
Scanner cc=new Scanner(System.in);
System.out.println("***********************");
System.out.println(" 学生 你好 ");
System.out.println("***********************");
System.out.println(" 1.查询信息 ");
System.out.println(" 2.显示信息 ");
System.out.println(" 0.返回 ");
System.out.println("***********************");
int t=cc.nextInt();
switch(t) {
case 1:search();
break;
case 2:print();
break;
case 0:menu();
break;
default:
System.out.println("输入错误 请重新输入：");
menu2();
break;
}
}
}
//增加
private static void add() {
String sql="insert into student value(?,?,?,?)";
Connection con = TheSqlconnection();
Scanner cc=new Scanner(System.in);
try {
System.out.println("------------------------");
System.out.println("请输入你要添加的学生个数：");
System.out.println("------------------------");
int n=cc.nextInt();
for(int p=1;p<=n;p++) {
System.out.println("请输入学生学号：");
int id=cc.nextInt();
System.out.println("请输入学生姓名：");
cc.nextLine();
String name=cc.nextLine();
System.out.println("请输入学生性别：");
String sex=cc.nextLine();
System.out.println("请输入学生成绩：");
int grade=cc.nextInt();
PreparedStatement ps = con.prepareStatement(sql);
//设置SQL语句里的参数
ps.setInt(1,id);
ps.setString(2,name);
ps.setString(3,sex);
ps.setInt(4,grade);
int rows=ps.executeUpdate();
if(rows>0) {
System.out.println("第"+p+"个学生信息添加成功！！");
System.out.println("============================");
}
}
}catch(SQLException e) {
e.printStackTrace();
}
}
//删除
private static void del() {
Connection con = TheSqlconnection();
Scanner cc=new Scanner(System.in);
try {
System.out.println("请输入想要删除的学生学号：");
int id=cc.nextInt();
String sql="delete from student where id="+id;
PreparedStatement ps = con.prepareStatement(sql);
int rows=ps.executeUpdate();
if(rows>0) {
System.out.println("删除学生信息成功！！");
System.out.println("============================");
}else {
System.out.println("删除学生信息失败！！");
}
}catch(SQLException e) {
e.printStackTrace();
}
}
//查询
private static void search() {
Connection con = TheSqlconnection();
Scanner cc=new Scanner(System.in);
try {
String sql="select * from student where id=?";
PreparedStatement ps = con.prepareStatement(sql);
System.out.println("请输入想要查询的学生学号：");
int id=cc.nextInt();
ps.setInt(1,id);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
String name=rs.getString("name");
String sex=rs.getString("sex");
int grade=rs.getInt("grade");
System.out.println("查询结果如下：");
System.out.println("============================");
System.out.println("id"+"\t"+"姓名"+"\t"+"性别"+"\t"+"成绩");
System.out.println(id+"\t"+name+"\t"+sex+"\t"+grade);
System.out.println("============================");
System.out.println("查询学生信息成功！！");
System.out.println("============================");
}
rs.close();
con.close();
}catch(SQLException e) {
System.out.println("查询学生信息失败！！");
e.printStackTrace();
}
}
//更新
private static void update() {
Connection con = TheSqlconnection();
Scanner cc=new Scanner(System.in);
try {
System.out.println("请输入想要更新的学生学号：");
int id=cc.nextInt();
System.out.println("请输入想要更新的学生姓名：");
cc.nextLine();
String name=cc.nextLine();
System.out.println("请输入想要更新的学生性别：");
String sex=cc.nextLine();
System.out.println("请输入想要更新的学生成绩：");
int grade=cc.nextInt();
String sql="update student set name=?,sex=?,grade=? where id=?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1,name);
ps.setString(2,sex);
ps.setInt(3,grade);
ps.setInt(4,id);
ps.executeUpdate();
System.out.println("更新学生信息成功！！");
System.out.println("============================");
ps.close();
}catch(SQLException e) {
System.out.println("更新学生信息失败！！");
e.printStackTrace();
}
}
//分页查询
private static void pages() {
Scanner cc=new Scanner(System.in);
Connection con = TheSqlconnection();
System.out.println("每页显示五条记录 请问你想查询哪一页(请输入大于等于1的整数)：");
int num=cc.nextInt();
int start=0;
int count=5;
try {
Statement ps = con.createStatement();
if (num==1) {
start = 0;
}else{
if (num<1) {
System.out.println("输入错误！ 无法查询");
System.out.println("=====================");
}else {
start = (num-1)*5;
}
}
String sql = "select * from student order by id asc limit "+ start + "," + count;
ResultSet rs = ps.executeQuery(sql);
System.out.println("id"+"\t"+"姓名"+"\t"+"性别"+"\t"+"成绩");
System.out.println("----------------------------");
while(rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
String sex = rs.getString(3);
int grade = rs.getInt(4);
System.out.println(id+"\t"+name+"\t"+sex+"\t"+grade);
}
}catch(SQLException e){
e.printStackTrace();
}
}
//分组排序
private static void order() {
Scanner cc=new Scanner(System.in);
Connection con = TheSqlconnection();
System.out.println("=====================");
System.out.println(" 1.升序 ");
System.out.println(" 2.降序 ");
System.out.println("=====================");
System.out.println("请选择排序的方式：");
int a=cc.nextInt();
if(a==1) {
try {
String sql="select * from student order by grade asc)";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql); //用来存放获取的结果集
System.out.println("----------------------------");
System.out.println("升序结果如下：");
System.out.println("----------------------------");
System.out.println("id"+"\t"+"姓名"+"\t"+"性别"+"\t"+"成绩");
System.out.println("----------------------------");
Integer id = null;
String name = null;
String sex = null;
Integer grade = null;
//rs.next()返回值为布尔型
while(rs.next()){
id = rs.getInt("id");
name = rs.getString("name");
sex = rs.getString("sex");
grade = rs.getInt("grade");
System.out.println(id + "\t" + name+ "\t" + sex+ "\t" + grade);
}
rs.close();
con.close();
}catch(SQLException e){
e.printStackTrace();
}
}else if(a==2) {
try {
String sql="select * from student order by grade desc";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql); //用来存放获取的结果集
System.out.println("----------------------------");
System.out.println("降序结果如下：");
System.out.println("----------------------------");
System.out.println("id"+"\t"+"姓名"+"\t"+"性别"+"\t"+"成绩");
System.out.println("----------------------------");
Integer id = null;
String name = null;
String sex = null;
Integer grade = null;
//rs.next()返回值为布尔型
while(rs.next()){
id = rs.getInt("id");
name = rs.getString("name");
sex = rs.getString("sex");
grade = rs.getInt("grade");
System.out.println(id + "\t" + name+ "\t" + sex+ "\t" + grade);
}
rs.close();
con.close();
}catch(SQLException e){
e.printStackTrace();
}
}
}
//显示
private static void print() {
String sql = "select * from student";//定义SQL语句
Connection con = TheSqlconnection();
try {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql); //用来存放获取的结果集
System.out.println("----------------------------");
System.out.println("数据库内容如下所示：");
System.out.println("----------------------------");
System.out.println("id"+"\t"+"姓名"+"\t"+"性别"+"\t"+"成绩");
System.out.println("----------------------------");
Integer id = null;
String name = null;
String sex = null;
Integer grade = null;
//rs.next()返回值为布尔型
while(rs.next()){
id = rs.getInt("id");
name = rs.getString("name");
sex = rs.getString("sex");
grade = rs.getInt("grade");
System.out.println(id + "\t" + name+ "\t" + sex+ "\t" + grade);
}
rs.close();
con.close();
}catch(SQLException e){
e.printStackTrace();
}
}
}
案例三
JDBCUtils.java
package vb.itcast.util;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private static String url;
private static String username;
private static String password;
private static String driver;
static {
try {
Properties pro =new Properties();
//类加载器
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
System.out.println(res);
// pro.load(new FileReader("src/jdbc.properties"));
pro.load(new FileReader(path));
url=pro.getProperty("url");
username=pro.getProperty("username");
password=pro.getProperty("password");
driver=pro.getProperty("driver");
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getconnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
// Class.forName("com.mysql.cj.jdbc.Driver");
// String url = "jdbc:mysql://localhost:3306/runnoob?&useSSL=false&serverTimezone=UTC" ;
// String username = "root" ;
// String password = "root" ;
public static void close(ResultSet rs, Statement stmt,Connection conn){
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt!=null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Statement stmt,Connection conn){
if( stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if( conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public class zxc{
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public int getAlexa() {
return alexa;
}
public void setAlexa(int alexa) {
this.alexa = alexa;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
private int id;
private String name;
private String url;
private int alexa;
private String country;
@Override
public String toString() {
return "zxc{" +
"id=" + id +
", name='" + name + '\'' +
", url='" + url + '\'' +
", alexa=" + alexa +
", country='" + country + '\'' +
'}';
}
}
}
JDBCDemo9.java
package vb.itcast.jdbc;
import vb.itcast.util.JDBCUtils;
import java.sql.*;
import java.util.Scanner;
public class JDBCDemo9 {
public static void main(String[] args) {
System.out.println("是否注册Y/N");
Scanner sc = new Scanner(System.in);
;
if (sc.nextLine().equals("Y")) {
registered();
}
System.out.println("登录");
System.out.println("请输入用户名");
String username = sc.nextLine();
System.out.println("请输入密码");
String password = sc.nextLine();
boolean flag = new JDBCDemo9().login(username, password);
if (flag) {
System.out.println("登录成功");
} else {
System.out.println("用户名或密码错误");
}
while (flag) {
System.out.println("---------------------------------------------------------------");
System.out.println("按1添加信息");
System.out.println("按2删除信息");
System.out.println("按3查找信息");
System.out.println("按4修改信息");
System.out.println("按5分组排序");
System.out.println("按6分页查询");
System.out.println("按0退出");
System.out.println("---------------------------------------------------------------");
int ch = sc.nextInt();
switch (ch) {
case 1:
insert();
break;
case 2:
delete();
break;
case 3:
select();
break;
case 4:
update();
break;
case 5:
order();
break;
case 6:
limit();
break;
case 0:
flag=false;
System.out.println("bye bye");
break;
default:
System.out.println("无此选项请重新输入");
}
}
}
public boolean login(String username, String password) {
if (username == null && password == null) {
return false;
}
String sql = "select * from user where username=? and password=?";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getconnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);
rs = pstmt.executeQuery();
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(rs, pstmt, conn);
}
return false;
}
static void registered() {
Scanner sc = new Scanner(System.in);
Connection conn = null;
PreparedStatement pstmt = null;
try {
String sql = "insert into user values (null ,?,?)";
conn = JDBCUtils.getconnection();
pstmt = conn.prepareStatement(sql);
System.out.println("输入账号");
pstmt.setString(1, sc.next());
System.out.println("输入密码");
pstmt.setString(2, sc.next());
int count = pstmt.executeUpdate();
if (count > 0) {
System.out.println("注册成功");
} else
System.out.println("注册失败");
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(pstmt, conn);
}
}
static void insert() {
Scanner sc = new Scanner(System.in);
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "insert into websites values (null ,?,?,?,?)";
try {
conn = JDBCUtils.getconnection();
pstmt = conn.prepareStatement(sql);
System.out.println("输入名称");
pstmt.setString(1, sc.next());
System.out.println("输入地址");
pstmt.setString(2, sc.next());
System.out.println("输入alexa");
pstmt.setString(3, sc.next());
System.out.println("输入地区");
pstmt.setString(4, sc.next());
int count = pstmt.executeUpdate();
if (count > 0) {
System.out.println("添加成功");
} else {
System.out.println("添加失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(pstmt, conn);
}
}
static void delete() {
Scanner sc = new Scanner(System.in);
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "delete from websites where id=?";
try {
conn = JDBCUtils.getconnection();
pstmt = conn.prepareStatement(sql);
System.out.println("请输入要删除的ID");
pstmt.setString(1, sc.next());
int count = pstmt.executeUpdate();
if (count > 0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(pstmt, conn);
}
}
static void select(){
Scanner sc = new Scanner(System.in);
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
String sql = "select *from websites where id=?";
try {
conn = JDBCUtils.getconnection();
pstmt = conn.prepareStatement(sql);
System.out.println("请输入要查询的ID");
pstmt.setString(1, sc.next());
rs=pstmt.executeQuery();
while (rs.next()){
int id=rs.getInt("id");
String name = rs.getString("name") ;
String pass = rs.getString("url") ;
String country=rs.getString("country");
int alexa = rs.getInt("alexa");
System.out.println(id+" "+name+" "+pass+" "+country+" "+alexa);
}
// if (rs.next()) {
// System.out.println("查询成功");
// } else {
// System.out.println("查询失败");
// }
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close( rs,pstmt, conn);
}
}
static void update(){
Scanner sc = new Scanner(System.in);
Connection conn = null;
PreparedStatement pstmt = null;
String sql = "update websites set name =?,url=?,alexa=?,country=? where id=?";
try {
conn = JDBCUtils.getconnection();
pstmt = conn.prepareStatement(sql);
System.out.println("请输入要修改的ID");
pstmt.setString(5,sc.next());
System.out.println("输入名称");
pstmt.setString(1, sc.next());
System.out.println("输入地址");
pstmt.setString(2, sc.next());
System.out.println("输入alexa");
pstmt.setString(3, sc.next());
System.out.println("输入地区");
pstmt.setString(4, sc.next());
int count = pstmt.executeUpdate();
if (count > 0) {
System.out.println("修改成功");
} else {
System.out.println("修改失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(pstmt, conn);
}
}
static void order(){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
String sql = "select country,SUM(alexa) FROM websites group by country order by SUM(alexa) desc ";
try {
conn = JDBCUtils.getconnection();
pstmt = conn.prepareStatement(sql);
rs=pstmt.executeQuery();
while (rs.next()){
String country=rs.getString("country");
int alexa = rs.getInt(2);
System.out.println(country+" "+alexa);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close( rs,pstmt, conn);
}
}static int page=1;
static void limit(){
Scanner sc = new Scanner(System.in);
Connection conn =null;
PreparedStatement pstmt =null;
ResultSet rs=null;
String sql="select *from websites limit ?,?";
try {System.out.println("请输入每页显示条数");
int size=sc.nextInt();
conn = JDBCUtils.getconnection();
pstmt = conn.prepareStatement(sql);
// System.out.println("请输入起始号");
pstmt.setInt(1,(page-1)*size);
pstmt.setInt(2,size);
rs=pstmt.executeQuery();
while (rs.next()){
int id=rs.getInt("id");
String name = rs.getString("name") ;
String pass = rs.getString("url") ;
String country=rs.getString("country");
int alexa = rs.getInt("alexa");
System.out.println(id+" "+name+" "+pass+" "+country+" "+alexa);
}
page++;
} catch (SQLException e) {
e.printStackTrace();
}
finally {
JDBCUtils.close(rs,pstmt,conn);
}
}
}
websites.sql
/*
Navicat Premium Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 80019
Source Host : localhost:3306
Source Schema : runnoob
Target Server Type : MySQL
Target Server Version : 80019
File Encoding : 65001
Date: 26/04/2020 23:53:01
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for websites
-- ----------------------------
DROP TABLE IF EXISTS `websites`;
CREATE TABLE `websites` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`name` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '站点名称',
`url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`alexa` int(0) NOT NULL DEFAULT 0 COMMENT 'Alexa 排名',
`country` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '国家',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 27 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of websites
-- ----------------------------
INSERT INTO `websites` VALUES (1, 'Google', 'https://www.google.cm/', 1, 'USA');
INSERT INTO `websites` VALUES (2, '淘宝', 'https://www.taobao.com/', 13, 'CN');
INSERT INTO `websites` VALUES (3, '菜鸟教程', 'http://www.runoob.com', 5892, 'CN');
INSERT INTO `websites` VALUES (4, '微博', 'http://weibo.com/', 20, 'CN');
INSERT INTO `websites` VALUES (5, 'Facebook', 'https://www.facebook.com/', 3, 'USA');
INSERT INTO `websites` VALUES (6, '2', '2', 2, '2');
INSERT INTO `websites` VALUES (7, '1', '1', 1, '1');
INSERT INTO `websites` VALUES (8, '1', '1', 1, '1');
INSERT INTO `websites` VALUES (9, '1', '1', 1, '1');
INSERT INTO `websites` VALUES (10, '1', '1', 1, '1');
INSERT INTO `websites` VALUES (11, '1', '1', 1, '1');
INSERT INTO `websites` VALUES (12, '1', '1', 1, '1');
INSERT INTO `websites` VALUES (13, '1', '1', 1, '1');
INSERT INTO `websites` VALUES (14, '1', '1', 1, '1');
INSERT INTO `websites` VALUES (15, '1', '1', 1, '1');
INSERT INTO `websites` VALUES (16, '1', '1', 1, '1');
INSERT INTO `websites` VALUES (17, '1', '1', 1, '1');
INSERT INTO `websites` VALUES (18, '1', '1', 1, '1');
INSERT INTO `websites` VALUES (19, 'country', '1', 1, '1');
INSERT INTO `websites` VALUES (20, '11', '22', 33, '1');
INSERT INTO `websites` VALUES (21, '汤不热', 'www.buzhidao.com', 2123, '米国');
INSERT INTO `websites` VALUES (22, '3', '3', 0, 'UK');
INSERT INTO `websites` VALUES (23, '3', '3', 0, 'UK');
INSERT INTO `websites` VALUES (24, '3', '3', 0, 'UK');
INSERT INTO `websites` VALUES (25, '3', '3', 0, 'UK');
INSERT INTO `websites` VALUES (30, '3', '3', 0, 'UK');
INSERT INTO `websites` VALUES (31, '1', '1', 1, '1');
SET FOREIGN_KEY_CHECKS = 1;
user.sql
/*
Navicat Premium Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 80019
Source Host : localhost:3306
Source Schema : runnoob
Target Server Type : MySQL
Target Server Version : 80019
File Encoding : 65001
Date: 26/04/2020 23:53:30
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`PASSWORD` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'zhangsan', '123');
INSERT INTO `user` VALUES (2, 'lisi', '234');
INSERT INTO `user` VALUES (3, 'lisi', '345');
INSERT INTO `user` VALUES (4, '111', '111');
INSERT INTO `user` VALUES (5, 'wangwu', '123456');
INSERT INTO `user` VALUES (6, '111', '111');
SET FOREIGN_KEY_CHECKS = 1;
jdbc.properties
url = jdbc:mysql://localhost:3306/runnoob?&useSSL=false&serverTimezone=UTC
username = root
password = root
driver =com.mysql.cj.jdbc.Driver
案例四
package com.itcast;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class StudentJDBC {
private static Scanner sc=new Scanner(System.in);
//注册
public static void login() {
System.out.println("");
String url="jdbc:mysql://localhost:3306/jt_db?useUnicode=true&characterEncoding=UTF8&useSSL=false";
String user="root";
String password="123456";
Scanner sc=new Scanner(System.in);
System.out.print("请输入你的注册用户名：");
String uname=sc.next();
System.out.print("请输入你的注册密码：");
String upwd=sc.next();
String sql="insert into login (uname,upwd) values (?,?)";
Connection con=null;
PreparedStatement ps=null;
try {
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection(url, user, password);
ps=con.prepareStatement(sql);
ps.setString(1, uname);
ps.setString(2,upwd);
int row=ps.executeUpdate();
if(row>0) {
System.out.println("注册成功！");
}else {
System.out.println("注册失败！");
}
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch (SQLException e) {
e.printStackTrace();
}finally {
//释放资源
try {
if(ps!=null) {
ps.close();
}
}catch(SQLException e){
e.printStackTrace();
}
try {
if(con!=null)
con.close();
}catch(SQLException e){
e.printStackTrace();
}
}
}
//登录
public static void register() {
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
Scanner sc=new Scanner(System.in);
System.out.print("请输入你的登陆用户名：");
String uname=sc.next();
System.out.print("请输入你的登陆密码：");
String upwd=sc.next();
try {
//注册驱动并获取连接
Class.forName("com.mysql.jdbc.Driver");//使用什么驱动连接数据库
String url="jdbc:mysql://localhost:3306/jt_db?useUnicode=true&characterEncoding=UTF8&useSSL=false";
String user="root";
String password="123456";
con=DriverManager.getConnection(url, user, password);
String sql="select count(*) from login where uname=? and upwd=?";
ps=con.prepareStatement(sql);
ps.setString(1, uname);
ps.setString(2,upwd);
rs=ps.executeQuery();
if(rs.next()) {
int count=rs.getInt(1);
if(count==0) {
System.out.println("登陆失败！");
}else {
System.out.println("登陆成功！");
}
}
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch (SQLException e) {
e.printStackTrace();
}finally {
//释放资源
try {
if(rs!=null)
rs.close();
}catch(SQLException e){
e.printStackTrace();
}
try {
if(ps!=null)
ps.close();
}catch(SQLException e){
e.printStackTrace();
}
try {
if(con!=null)
con.close();
}catch(SQLException e){
e.printStackTrace();
}
}
}
public static void main(String[] args) {
login();
register();
System.out.println("欢迎使用学生信息管理系统！");
while(true) {
System.out.print("1:查询学生信息 ");
System.out.print("2:添加学生信息 ");
System.out.print("3:修改学生信息 ");
System.out.print("4:删除学生信息 ");
System.out.print("5.分组排序 ");
System.out.println("6.分页查看 ");
System.out.print("请输入你要进行的操作：");
//接受用户输入的操作选项
String opt = sc.next();
if(opt.equals("1")) {
//查询学生信息
findAll();
}else if(opt.equals("2")) {
//添加学生信息
addStu();
}else if(opt.equals("3")) {
//修改学生信息
updateStu();
}else if(opt.equals("4")) {
//删除学生信息
deleteStu();
}else if(opt.equals("5")) {
//删除学生信息
order();
}else if(opt.equals("6")) {
//删除学生信息
findpage();
}else {
System.out.println("输入错误！请重新输入...");
}
}
}
//查询所有的学生信息，直接打印在控制台
private static void findAll(){
Connection con=null;
Statement stmt=null;
ResultSet rs=null;
try {
//注册驱动并获取连接
Class.forName("com.mysql.jdbc.Driver");//使用什么驱动连接数据库
String url="jdbc:mysql://localhost:3306/jt_db?useUnicode=true&characterEncoding=UTF8&useSSL=false";
String user="root";
String password="123456";
con=DriverManager.getConnection(url, user, password);
stmt=con.createStatement();
rs=stmt.executeQuery("select * from stu");
//列的索引从1开始
while(rs.next()) {
System.out.println(rs.getInt(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getInt(4));
}
System.out.println("=========================");
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放资源
try {
if(rs!=null)
rs.close();
}catch(SQLException e){
e.printStackTrace();
}
try {
if(stmt!=null)
stmt.close();
}catch(SQLException e){
e.printStackTrace();
}
try {
if(con!=null)
con.close();
}catch(SQLException e){
e.printStackTrace();
}
}
}
//添加学生信息
private static void addStu() {
System.out.println("请输入要添加的学生编号、姓名、性别、成绩(以空格分隔)：");
//获取用户输入的学生信息
int id=sc.nextInt();
String name=sc.next();
String gender=sc.next();
double score=sc.nextDouble();
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
//注册驱动并获取连接
Class.forName("com.mysql.jdbc.Driver");//使用什么驱动连接数据库
String url="jdbc:mysql://localhost:3306/jt_db?useUnicode=true&characterEncoding=UTF8&useSSL=false";
String user="root";
String password="123456";
con=DriverManager.getConnection(url, user, password);
//获取传输器，执行SQL语句
String sql="insert into stu value(?,?,?,?)";
ps=con.prepareStatement(sql);
//设置SQl语句中的参数
ps.setInt(1,id);
ps.setString(2, name);
ps.setString(3,gender);
ps.setDouble(4, score);
//执行sql语句
int rows=ps.executeUpdate();//不要二次传入SQL
if(rows>0) {
System.out.println("学生信息添加成功！");
System.out.println("=========================");
}
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放资源
try {
if(rs!=null)
rs.close();
}catch(SQLException e){
e.printStackTrace();
}
try {
if(ps!=null)
ps.close();
}catch(SQLException e){
e.printStackTrace();
}
try {
if(con!=null)
con.close();
}catch(SQLException e){
e.printStackTrace();
}
}
}
//修改学生信息
private static void updateStu() {
System.out.println("请输入要修改的学生编号、姓名、性别、成绩(以空格分隔)：");
int id=sc.nextInt();
String name=sc.next();
String gender=sc.next();
double score=sc.nextDouble();
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
//注册驱动并获取连接
Class.forName("com.mysql.jdbc.Driver");//使用什么驱动连接数据库
String url="jdbc:mysql://localhost:3306/jt_db?useUnicode=true&characterEncoding=UTF8&useSSL=false";
String user="root";
String password="123456";
con=DriverManager.getConnection(url, user, password);
//获取传输器，执行SQL语句
String sql="update stu set name=?,gender=?,score=? where id=?";
ps=con.prepareStatement(sql);
//设置SQL语句中的参数
ps.setString(1, name);
ps.setString(2,gender);
ps.setDouble(3, score);
ps.setInt(4,id);
//执行SQl语句
int rows=ps.executeUpdate();
if(rows>0) {
System.out.println("学生信息修改成功!");
System.out.println("=========================");
}
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放资源
try {
if(rs!=null)
rs.close();
}catch(SQLException e){
e.printStackTrace();
}
try {
if(ps!=null)
ps.close();
}catch(SQLException e){
e.printStackTrace();
}
try {
if(con!=null)
con.close();
}catch(SQLException e){
e.printStackTrace();
}
}
}
//删除学生信息
private static void deleteStu() {
System.out.println("请输入要删除的学生编号：");
int id=sc.nextInt();
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
//注册驱动并获取连接
Class.forName("com.mysql.jdbc.Driver");//使用什么驱动连接数据库
String url="jdbc:mysql://localhost:3306/jt_db?useUnicode=true&characterEncoding=UTF8&useSSL=false";
String user="root";
String password="123456";
con=DriverManager.getConnection(url, user, password);
//获取传输器，执行SQL语句
String sql="delete from stu where id=?";
ps=con.prepareStatement(sql);
//设置SQl语句的参数
ps.setInt(1, id);
//执行SQL语句
int rows=ps.executeUpdate();
if(rows>0) {
System.out.println("学生信息删除成功!");
System.out.println("=========================");
}
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放资源
try {
if(rs!=null)
rs.close();
}catch(SQLException e){
e.printStackTrace();
}
try {
if(ps!=null)
ps.close();
}catch(SQLException e){
e.printStackTrace();
}
try {
if(con!=null)
con.close();
}catch(SQLException e){
e.printStackTrace();
}
}
}
//分组排序
private static void order() {
Connection con=null;
Statement stmt=null;
ResultSet rs=null;
try {
//注册驱动并获取连接
Class.forName("com.mysql.jdbc.Driver");//使用什么驱动连接数据库
String url="jdbc:mysql://localhost:3306/jt_db?useUnicode=true&characterEncoding=UTF8&useSSL=false";
String user="root";
String password="123456";
con=DriverManager.getConnection(url, user, password);
//获取传输器，执行SQL语句
stmt=con.createStatement();
rs=stmt.executeQuery("select * from (select * from stu order by gender desc limit 999999 ) a group by a.score");
//设置SQl语句的参数
//执行SQL语句
while(rs.next()) {
System.out.println(rs.getInt(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getInt(4));
}
System.out.println("=========================");
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放资源
try {
if(rs!=null)
rs.close();
}catch(SQLException e){
e.printStackTrace();
}
try {
if(stmt!=null)
stmt.close();
}catch(SQLException e){
e.printStackTrace();
}
try {
if(con!=null)
con.close();
}catch(SQLException e){
e.printStackTrace();
}
}
}
//分页查询
/*
@param page 页数
*/
private static void findpage() {
Connection con=null;
Statement stmt=null;
ResultSet rs=null;
try {
//注册驱动并获取连接
Class.forName("com.mysql.jdbc.Driver");//使用什么驱动连接数据库
String url="jdbc:mysql://localhost:3306/jt_db?useUnicode=true&characterEncoding=UTF8&useSSL=false";
String user="root";
String password="123456";
con=DriverManager.getConnection(url, user, password);
stmt=con.createStatement();
rs=stmt.executeQuery("select * from stu where id<100 order by id asc limit 0,10");
//列的索引从1开始
while(rs.next()) {
System.out.println(rs.getInt(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getInt(4));
}
System.out.println("=========================");
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放资源
try {
if(rs!=null)
rs.close();
}catch(SQLException e){
e.printStackTrace();
}
try {
if(stmt!=null)
stmt.close();
}catch(SQLException e){
e.printStackTrace();
}
try {
if(con!=null)
con.close();
}catch(SQLException e){
e.printStackTrace();
}
}
}
}
案例五
package student;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class studentss {
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
Scanner in=new Scanner(System.in);
System.out.println("********用户界面********");
System.out.println("请选择：\n 1:用户登录\n 2:用户注册");
System.out.println("**********************");
int i=in.nextInt();
switch(i) {
case 1:
login();
break;
case 2:
register();
break;
default:
System.out.println("输入有误!");
System.exit(0);
}
int j;
for(j=0;j<1000;j++) {
menu();
System.out.println("请输入你想实现的功能所对应的序号:");
int a=in.nextInt();
switch(a) {
case 1:
add();
break;
case 2:
update();
break;
case 3:
delete();
break;
case 4:
select();
break;
case 5:
print();
break;
case 6:
select_1();
break;
case 7:
select_2();
break;
case 8:
select_3();
break;
}
}
}
private static void print() {
String sql="select * from students";
Connection conn =SqlConnection();
try {
Statement a=conn.createStatement();
ResultSet rs=a.executeQuery(sql);
System.out.println("————————————————————————————————");
System.out.println("数据库显示内容如下:");
System.out.println("————————————————————————————————");
System.out.println("id"+" "+"姓名"+" "+"性别"+" "+"成绩");
System.out.println("———————————————————————————————");
Integer id =null;
String name=null;
String ssex=null;
Integer grade=null;
while(rs.next()) {
id=rs.getInt("id");
name=rs.getString("name");
ssex=rs.getString("ssex");
grade=rs.getInt("grade");
System.out.println(id+" "+name+" "+ssex+" "+grade);
}
rs.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
private static void menu()
{
System.out.println("***************************************************");
System.out.println("*********\t学生信息管理系统\t********");
System.out.println("********\t1.增加学生信息\t***********");
System.out.println("********\t2.更新学生信息\t***********");
System.out.println("********\t3.删除学生信息\t***********");
System.out.println("********\t4.查询学生信息\t***********");
System.out.println("********\t5.打印学生信息\t***********");
System.out.println("********\t6.分组查询学生信息(降序)\t***********");
System.out.println("********\t7.分组查询学生信息(升序)\t***********");
System.out.println("********\t8.分页查询学生信息\t***********");
}
private static void add()
{
String sql="insert into students values(?,?,?,?)";
Connection conn =SqlConnection();
Scanner in=new Scanner(System.in);
try {
System.out.println("————————————————————————————");
System.out.println("请输入你要添加的学生个数:");
System.out.println("—————————————————————————————");
int j=in.nextInt();
for(int i=0;i<j;i++) {
System.out.println("请输入学生学号:");
int id=in.nextInt();
System.out.println("请输入学生姓名:");
in.nextLine();
String name=in.nextLine();
System.out.println("请输入学生性别:");
String ssex=in.nextLine();
System.out.println("请输入学生成绩:");
int grade=in.nextInt();
PreparedStatement ps =conn.prepareStatement(sql);
ps.setInt(1, id);
ps.setString(2, name);
ps.setString(3,ssex);
ps.setInt(4, grade);
int rows=ps.executeUpdate();
if(rows>0) {
System.out.println("添加成功!");
}
}
}catch(SQLException e) {
e.printStackTrace();
}
}
public static void delete() {
String sql="delete from students where id=?";
Scanner in=new Scanner(System.in);
Connection conn =SqlConnection();
try {
PreparedStatement ps =conn.prepareStatement(sql);
System.out.println("——————————————————————————————————");
System.out.println("请输入你想要删除的学生的学号:");
int id=in.nextInt();
ps.setInt(1, id);
int a=ps.executeUpdate();
if(a>0) {
System.out.println("删除成功!");
}
}catch(SQLException e) {
e.printStackTrace();
System.out.println("删除失败!");
}
}
public static void select() throws SQLException{
Connection conn =SqlConnection();
Scanner in=new Scanner(System.in);
String sql="select name,ssex,grade from students where id=?";
PreparedStatement ps =conn.prepareStatement(sql);
System.out.println("请输入你想要查询的学生所对应的学号:");
int id=in.nextInt();
ps.setInt(1,id);
ResultSet rs=ps.executeQuery();
while(rs.next()) {
String name=rs.getString("name");
String ssex=rs.getString("ssex");
int grade=rs.getInt("grade");
System.out.println(name+" "+ssex+" "+grade);
}
rs.close();
ps.close();
conn.close();
}
public static void update() {
System.out.println("请输入要更新的学生学号:");
Scanner in=new Scanner(System.in);
int id=in.nextInt();
System.out.println("请输入要更新的学生姓名:");
String name=in.next();
System.out.println("请输入要更新的学生性别:");
String ssex=in.next();
System.out.println("请输入要更新的学生成绩:");
int grade=in.nextInt();
try {
Connection conn=SqlConnection();
String sql="update students set name=?,ssex=?,grade=? where id=?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2,ssex);
ps.setInt(3,grade);
ps.setInt(4, id);
int a=ps.executeUpdate();
if(a>0) {
System.out.println("学生信息修改成功!");
}
}catch(SQLException e) {
e.getStackTrace();
}
}
private static void select_1() {
String sql="select * from students group by id,ssex order by grade desc;";
Connection conn =SqlConnection();
try {
PreparedStatement ps=conn.prepareStatement(sql);
Statement a=conn.createStatement();
ResultSet rs=a.executeQuery(sql);
System.out.println("————————————————————————————————");
System.out.println("数据库显示内容如下:");
System.out.println("————————————————————————————————");
System.out.println("id"+" "+"姓名"+" "+"性别"+" "+"成绩");
System.out.println("———————————————————————————————");
Integer id =null;
String name=null;
String ssex=null;
Integer grade=null;
ps.execute();
while(rs.next()) {
id=rs.getInt("id");
name=rs.getString("name");
ssex=rs.getString("ssex");
grade=rs.getInt("grade");
System.out.println(id+" "+name+" "+ssex+" "+grade);
}
rs.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
private static void select_2() {
String sql="select * from students group by id,ssex order by grade asc;";
Connection conn =SqlConnection();
try {
Statement a=conn.createStatement();
ResultSet rs=a.executeQuery(sql);
PreparedStatement ps=conn.prepareStatement(sql);
System.out.println("————————————————————————————————");
System.out.println("数据库显示内容如下:");
System.out.println("————————————————————————————————");
System.out.println("id"+" "+"姓名"+" "+"性别"+" "+"成绩");
System.out.println("———————————————————————————————");
Integer id =null;
String name=null;
String ssex=null;
Integer grade=null;
ps.execute();
while(rs.next()) {
id=rs.getInt("id");
name=rs.getString("name");
ssex=rs.getString("ssex");
grade=rs.getInt("grade");
System.out.println(id+" "+name+" "+ssex+" "+grade);
}
rs.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
private static void select_3() {
Scanner in=new Scanner(System.in);
Connection conn =SqlConnection();
System.out.println("每页显示六条记录，请输入你想查询的页对应的序号:");
int a=in.nextInt();
int b = 0;
int c=6;
try {
Statement ps=conn.createStatement();
if(a==1) {
b = 0;
}else {
if(a<1) {
System.out.println("输入错误，无法进行查询");
}else {
b=(a-1)*6;
}
}
String sql="select * from students order by id asc limit "+ b +"," + c;
ResultSet rs=ps.executeQuery(sql);
System.out.println("学号"+" "+"姓名"+" "+"性别"+" "+"成绩");
System.out.println("————————————————————————————————————————");
while(rs.next()) {
int id=rs.getInt(1);
String name=rs.getString(2);
String ssex=rs.getString(3);
int grade=rs.getInt(4);
System.out.println(id+" "+name+" "+ssex+" "+grade);
}
}catch(SQLException e) {
e.printStackTrace();
}
}
public static void register() throws SQLException{
Connection conn =SqlConnection();
Scanner in=new Scanner(System.in);
System.out.println("请输入你的姓名:");
String name=in.nextLine();
System.out.println("请输入你的登陆密码:");
String p1=in.next();
System.out.println("请再次输入你的登陆密码:");
String p2=in.next();
if(p1.equals(p2)) {
String password=p1;
String sql="insert into user(name,password) values(?,?)";
PreparedStatement pt = conn.prepareStatement(sql);
pt.setString(1,name);
pt.setString(2,password);
pt.execute();
System.out.println("注册成功!\n请登录:");
login();
}
else {
System.out.println("你输入的密码与确认密码不相同，请重新注册:");
register();
}
}
public static void login() throws SQLException{
Connection conn =SqlConnection();
Scanner in=new Scanner(System.in);
System.out.println("请输入你的姓名:");
String name=in.nextLine();
System.out.println("请输入你的密码:");
String password=in.next();
String sql = "select name,password from user where name=? and password=?";
PreparedStatement pt = conn.prepareStatement(sql);
pt.setString(1, name);
pt.setString(2, password);
ResultSet rs = pt.executeQuery();
//从给出的账号密码来检测是否有相同的账号密码
if(rs.next()) {
System.out.println("登陆成功!");
}else {
System.out.println("姓名或密码错误!\n请重新登陆:");
login();
}
}
public static Connection SqlConnection() {
Connection conn=null;
try {
//加载MySQL驱动
Class.forName("com.mysql.jdbc.Driver");
System.out.println("加载驱动成功!");
//连接数据库，获得连接对象
conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","a123456");
System.out.println("数据库连接成功");
// 创建执行环境
/*Statement statement=conn.createStatement();
// 执行sql语句，得到结果集
ResultSet result = statement.executeQuery("select * from students");
while(result.next()) {
System.out.print(result.getInt("id")+" ");
System.out.print(result.getString("name")+" ");
System.out.println(result.getString("password"));
}*/
}catch(Exception e) {
e.printStackTrace();
System.out.println("驱动加载失败");
System.out.println("数据库连接失败");
}
return conn;
}
}
案例六
DBtool.java
package com.it666.jdbc.conn;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBtool {
private static String driver;
private static String url;
private static String user;
private static String password;
public static Connection open()
{
try {
driver="com.mysql.jdbc.Driver";
url="jdbc:mysql://localhost:3306/student";
user="root";
password="yang1234";
Class.forName(driver);//注册驱动
return DriverManager.getConnection(url, user, password);//连接到数据库
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void close(Connection conn)
{
if(conn!=null)
{
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Test.java
package com.it666.jdbc.conn;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class Test { //主函数
public static void main(String[]args ){
@SuppressWarnings("resource")
Scanner in=new Scanner(System.in);
logan();
boolean b=true;
while(b){
menu();
int a=in.nextInt();
switch(a) {
case 1:insert();break;
case 2:delete();break;
case 3:query();break;
case 4:update();break;
case 5:show();break;
case 6:rank();break;
case 7:fenye();break;
case 8:b=false;System.out.println("再见，谢谢您的使用");break;
default:System.out.println("选择错误，请重新选择！");break;
}
}
}
static void update() { //修改信息
Connection conn=DBtool.open();
try {
@SuppressWarnings("resource")
Scanner in=new Scanner(System.in);
@SuppressWarnings("resource")
Scanner s=new Scanner(System.in);
String sql;
String sno;
String sname;
String ssex;
String sage;
String sscore;
System.out.println("请输入你那个被修改学生的学号：");
sno=s.nextLine();
System.out.println("请选择你要修改的信息的选项：");
System.out.println("1.学号 2.姓名 3性别 4年龄 5.成绩");
int a=in.nextInt();
switch(a) {
case 1:
System.out.println("请输入要修改的学号");
String sno1=s.nextLine();
sql="update student set Sno=\""+sno1+"\"where Sno=\""+sno+"\";";
PreparedStatement pst=(PreparedStatement)conn.prepareStatement(sql);
@SuppressWarnings("unused") int i=pst.executeUpdate();
//System.out.println(sql);
System.out.println("修改成功！");
break;
case 2:
System.out.println("请输入要修改的姓名");
sname=s.nextLine();
sql="update student set Sname=\""+sname+"\"where Sno=\""+sno+"\";";
PreparedStatement ps=(PreparedStatement)conn.prepareStatement(sql);
@SuppressWarnings("unused") int j=ps.executeUpdate();
// System.out.println(j);
System.out.println("修改成功");
break;
case 3:
System.out.println("请输入要修改的性别");
ssex=s.nextLine();
sql="update student set Ssex=\""+ssex+"\"where Sno=\""+sno+"\";";
PreparedStatement p=(PreparedStatement)conn.prepareStatement(sql);
@SuppressWarnings("unused") int k=p.executeUpdate();
// System.out.println(k);
System.out.println("修改成功");
break;
case 4:
System.out.println("请输入要修改的年龄");
sage=s.nextLine();
sql="update student set Sage=\""+sage+"\"where Sno=\""+sno+"\";";
PreparedStatement t=(PreparedStatement)conn.prepareStatement(sql);
@SuppressWarnings("unused") int l=t.executeUpdate();
// System.out.println(k);
System.out.println("修改成功");
break;
case 5:
System.out.println("请输入要修改的成绩");
sscore=s.nextLine();
sql="update student set Stel=\""+sscore+"\"where Sno=\""+sno+"\";";
PreparedStatement r=(PreparedStatement)conn.prepareStatement(sql);
@SuppressWarnings("unused") int m=r.executeUpdate();
// System.out.println(k);
System.out.println("修改成功");
break;
default:System.out.println("输入错误！返回主界面");break;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBtool.close(conn);
}
}
static void menu() { //菜单
System.out.println("=========学生信息表==========");
System.out.println("======请输入你想实现的功能：==");
System.out.println("*******==1:增加信息==*********");
System.out.println("*******==2:删除信息==*********");
System.out.println("*******==3:查询信息==*********");
System.out.println("*******==4:修改信息==*********");
System.out.println("*******==5.显示表中信息==****");
System.out.println("*******==6:按性别分组排序==*********");
System.out.println("*******==7:分页查询信息==*********");
System.out.println("*******==8.退出==************");
}
static void show() { //显示信息
Connection conn=DBtool.open();
String sql="select * from student;";
try {
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);
System.out.println("学号-------------姓名---------------性别-----------年龄---------------成绩");
while(rs.next())
{
String sno=rs.getString(1);
String sname=rs.getString(2);
String ssex=rs.getString(3);
String sage=rs.getString(4);
String sscore=rs.getString(5);
System.out.println(sno+" "+sname+" "+ssex+" "+sage+" "+sscore);
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("加载驱动失败");
}finally {
DBtool.close(conn);
}
}
static void delete() { //删除信息
@SuppressWarnings("resource")
Scanner in=new Scanner(System.in);
System.out.println("请输入要删除的学生信息的学号");
Connection conn=DBtool.open();
String sql=in.nextLine();
try {
Statement stmt=conn.createStatement();
stmt.executeUpdate("delete from student where Sno="+sql);
System.out.println("删除成功！");
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBtool.close(conn);
}
}
static void insert() { //增加信息
Connection conn=DBtool.open();
String sno=null;
String sname=null;
String ssex=null;
int sage=0;
String stel=null;
@SuppressWarnings("resource")
Scanner in=new Scanner(System.in);
System.out.println("请输入添加学生的学号");
sno=in.nextLine();
System.out.println("请输入添加学生的姓名");
sname=in.nextLine();
System.out.println("请输入添加学生的性别");
ssex=in.nextLine();
System.out.println("请输入添加学生的年龄");
@SuppressWarnings("resource")
Scanner a=new Scanner(System.in);
sage=a.nextInt();
System.out.println("请输入添加学生的成绩");
stel=in.nextLine();
try {
String sql="insert into student (Sno,Sname,Ssex,Sage,Sscore)values(?,?,?,?,?)";
PreparedStatement pst=conn.prepareStatement(sql);
pst.setString(1, sno);
pst.setString(2, sname);
pst.setString(3,ssex);
pst.setInt(4, sage);
pst.setString(5, stel);
@SuppressWarnings("unused")
int i=pst.executeUpdate();
System.out.println("添加成功");
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBtool.close(conn);
}
}
static void query() { //查询信息
@SuppressWarnings("resource")
Scanner in=new Scanner(System.in);
System.out.println("请输入你要查询学生的学号");
String sno=in.nextLine();
String sql="select * from student where Sno=\""+sno+"\";";
Connection conn=DBtool.open();
try {
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);
System.out.println("学号-------------姓名---------------性别--------------年龄---------------成绩");
while(rs.next())
{
sno=rs.getString(1);
String sname=rs.getString(2);
String ssex=rs.getString(3);
String sage=rs.getString(4);
String sscore=rs.getString(5);
System.out.println(sno+" "+sname+" "+ssex+" "+sage+" "+sscore);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBtool.close(conn);
}
}
static void createtable() {
Connection conn=DBtool.open();
String sql="create table student(Sno char(10) primary key ,Sname varchar(20),Ssex char(4),Sage int,Sscore char(11));";
try {
Statement stmt=conn.createStatement();
stmt.execute(sql);
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBtool.close(conn);
}
}
static void logan() {
@SuppressWarnings("resource")
Scanner input=new Scanner(System.in);
String account;
String password;
do {
System.out.println("请输入账号和密码，中间用空格间隔:");
account=input.next();
password=input.next();
if(!(account.equals("admin"))) {
System.out.println("账号输入错误！请重新输入账号和密码");
}else if(!(password.equals("admin"))) {
System.out.println("密码输入错误！请重新输入账号和密码");
}
}while(!(account.equals("admin")&&password.equals("admin")));
System.out.println("成功登陆！！！");
}
static void rank() { //分组排序信息
Connection conn=DBtool.open();
try {
Statement stmt=conn.createStatement();
stmt.executeUpdate("select * from student group by Ssex order by Sscore desc;");
System.out.println("排序成功！");
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBtool.close(conn);
}
}
static void fenye() { //分页查询信息
Connection conn=DBtool.open();
String sql="select * from student limit 0,3;";
try {
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);
System.out.println("学号-----姓名----------性别--------年龄--------成绩");
while(rs.next())
{
String sno=rs.getString(1);
String sname=rs.getString(2);
String ssex=rs.getString(3);
String sage=rs.getString(4);
String sscore=rs.getString(5);
System.out.println(sno+" "+sname+" "+ssex+" "+sage+" "+sscore);
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("加载驱动失败");
}finally {
DBtool.close(conn);
}
}
}
案例七
package com.atguigu3.been;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.Scanner;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import org.junit.Test;
import com.atguigu3.util.JDBCUtils;
public class Customer {
private static Scanner scanner;
private static Object balance;
private int id;
private String name;
private String email;
private Date birth;
public Customer()
{
super();
}
public Customer(int id,String name,String email,Date birth)
{
super();
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
public int getId()
{
return id;
}
public void setId(int id)
{
this.id = id;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public String getEmail()
{
return email;
}
public void setEmail(String email)
{
this.email = email;
}
public Date getBirth()
{
return birth;
}
public void setBirth(Date birth)
{
this.birth = birth;
}
@Override
public String toString()
{
return "Customer[id="+id+",name="+name+",email="+email+",birth="+birth+"]";
}
public static void main(String[] args)
{
// TODO Auto-generated method stub
Scanner in=new Scanner(System.in);
System.out.println("----------用户界面---------------");
System.out.println("----------1:用户登录-------------");
System.out.println("----------2:用户注册-------------");
System.out.println("----------3:退出------------------");
System.out.println("---------------------------");
int i=in.nextInt();
switch(i) {
case 1:
login();
break;
case 2:
register();
break;
case 3:
balance = false;
break;
default:
System.out.println("输入有误!");
System.exit(0);
}
int j;
for(j=0;j<1000;j++) {
menu();
System.out.println("请输入所选功能的序号:");
int a=in.nextInt();
switch(a) {
case 1:
add();
break;
case 2:
update();
break;
case 3:
delete();
break;
case 4:
select();
break;
case 5:
select_1();
break;
case 6:
select_2();
break;
case 7:
select_3();
}
}
}
private static void menu() {
// TODO Auto-generated method stub
System.out.println("--------------------------------------------");
System.out.println("---------\t学生信息管理系统\t---------");
System.out.println("---------\t1.增加学生信息\t---------");
System.out.println("---------\t2.更新学生信息\t---------");
System.out.println("---------\t3.删除学生信息\t---------");
System.out.println("---------\t4.查询学生信息\t---------");
System.out.println("---------\t5.分组查询学生信息(降序)\t---------");
System.out.println("---------\t6.分组查询学生信息(升序)\t---------");
System.out.println("---------\t7.分页查询学生信息\t---------");
}
// //通用的增删改操作(对与数据库信息的增删查该）
// public void update(String sql,Object ...args) {//sql中占位符的个数与可用形参的长度相同
// Connection conn = null;
// PreparedStatement ps = null;
// try {
// //1.获取数据库的连接
// conn = JDBCUtils.getConnection();
// //2.预编译sql语句，返回PreparedStatement的实例
// ps = conn.prepareStatement(sql);
// //3.填充占位符
// for(int i = 0;i<args.length;i++)
// {
// ps.setObject(i+1, args[i]);//参数声明会产生错误
// }
// //4.执行sql语句
// ps.execute();
// } catch (Exception e) {
// e.printStackTrace();
// }finally {
// //关闭资源
// JDBCUtils.closeResource(conn, ps);
// }
//
//
// }
//增加数据
private static void add() {
// TODO Auto-generated method stub
String sql="insert into customers (id,name,email,birth) values(?,?,?,?)";
Connection conn = null;
Scanner in = new Scanner(System.in);
try {
//获取数据库的连接
System.out.println("------------------------------");
System.out.println("请输入你要添加的学生个数:");
System.out.println("------------------------------");
int j=in.nextInt();
for(int i = 0;i < j;i++) {
System.out.println("请输入学生学号:");
int id = in.nextInt();
System.out.println("请输入学生姓名:");
in.nextLine();
String name = in.nextLine();
System.out.println("请输入学生邮件:");
String email = in.nextLine();
System.out.println("请输入学生生日:");
int birth = in.nextInt();
//预编译sql语句，返回PreparedStatement的实例
PreparedStatement ps =conn.prepareStatement(sql);
//执行sql语句
ps.setInt(1, id);
ps.setString(2, name);
ps.setString(3,email);
ps.setInt(4, birth);
int rows=ps.executeUpdate();
if(rows>0) {
System.out.println("添加成功!");
}
}
}catch(Exception e) {
e.printStackTrace();
}
}
//修改数据
private static void update() {
// TODO Auto-generated method stub
String sql = "update customers set name = ? where id = ?";
Scanner in = new Scanner(System.in);
Connection conn = null;
try {
//预编译sql语句，返回PreparedStatement的实例
PreparedStatement ps = conn.prepareStatement(sql);
System.out.println("---------------------------");
System.out.println("请输入你想要更新的学生的学号:");
int id = in.nextInt();
System.out.println("请输入你想要更新的学生的姓名:");
in.nextLine();
String name = in.nextLine();
System.out.println("请输入你想要更新的学生邮件:");
String email = in.nextLine();
System.out.println("请输入你想要更新的学生生日:");
int birth = in.nextInt();
//执行sql语句
ps.setInt(1, id);
ps.setString(2, name);
ps.setString(3, email);
ps.setInt(4, birth);
ps.executeUpdate();
System.out.println("更新成功!");
}catch(Exception e){
e.printStackTrace();
System.out.println("更新失败!");
}
}
//删除数据
private static void delete() {
// TODO Auto-generated method stub
String sql="delete from costomers where id=?";
Scanner in = new Scanner(System.in);
Connection conn = null;
try {
//预编译sql语句，返回PreparedStatement的实例
PreparedStatement ps = conn.prepareStatement(sql);
System.out.println("------------------------------");
System.out.println("请输入你想要删除的学生的学号:");
int id = in.nextInt();
//执行sql语句
ps.setInt(1, id);
int a = ps.executeUpdate();
if(a > 0) {
System.out.println("删除成功!");
}
}catch(Exception e) {
e.printStackTrace();
System.out.println("删除失败!");
}
}
//针对于Customers表的查询操作
private static void select() {
// TODO Auto-generated method stub
Connection conn = null;
PreparedStatement ps = null;
//获取结果集的元数据
ResultSet rs = null;
Scanner in = new Scanner(System.in);
String sql = "select id,name,birth,email from customers where id = ?";
try {
ps = conn.prepareStatement(sql);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("请输入你想要查询的学生所对应的学号:");
int id = in.nextInt();
try {
ps.setInt(1,id);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
while(rs.next()) {
//获取每个列的列名
String name = rs.getString("name");
String email = rs.getString("email");
int birth = rs.getInt("grade");
System.out.println(name + " " + email + " " + birth);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
rs.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//分组查询学生信息(降序)
private static void select_1() {
// TODO Auto-generated method stub
String sql="select * from costomers order by email 1111@126.com;";
Connection conn = null;
try {
//预编译sql语句
Statement a=conn.createStatement();
//获取结果集的元数据
ResultSet rs=a.executeQuery(sql);
System.out.println("------------------------");
System.out.println("数据库中的内容显示：");
System.out.println("------------------------");
System.out.println("学号"+" "+"姓名"+" "+"邮箱"+" "+"生日");
System.out.println(" ");
Integer id =null;
String name=null;
String email=null;
Integer birth=null;
while(rs.next()) {
id=rs.getInt("id");
name=rs.getString("name");
email=rs.getString("email");
birth=rs.getInt("birth");
System.out.println(id+" "+name+" "+email+" "+birth);
}
rs.close();
conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
//分组查询学生信息(升序)
private static void select_2() {
// TODO Auto-generated method stub
String sql="select * from costomers order by email 3333@126.com;";
Connection conn = null;
try {
//预编译sql语句
Statement a=conn.createStatement();
//获取结果集的元数据
ResultSet rs=a.executeQuery(sql);
System.out.println("------------------------");
System.out.println("数据库中的内容显示：");
System.out.println("------------------------");
System.out.println("学号"+" "+"姓名"+" "+"邮箱"+" "+"生日");
System.out.println(" ");
Integer id =null;
String name=null;
String email=null;
Integer birth=null;
while(rs.next()) {
id=rs.getInt("id");
name=rs.getString("name");
email=rs.getString("email");
birth=rs.getInt("birth");
System.out.println(id+" "+name+" "+email+" "+birth);
}
rs.close();
conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
//分页查询学生信息
private static void select_3() {
// TODO Auto-generated method stub
String sql="select * from costomers;";
Connection conn = null;
try {
//预编译sql语句
Statement a=conn.createStatement();
//获取结果集的元数据
ResultSet rs=a.executeQuery(sql);
System.out.println("------------------------");
System.out.println("数据库显示内容如下:");
System.out.println("------------------------");
System.out.println("id"+" "+"姓名"+" "+"邮箱"+" "+"生日");
System.out.println(" ");
Integer id =null;
String name=null;
String email=null;
Integer birth=null;
while(rs.next()) {
id=rs.getInt("id");
name=rs.getString("name");
email=rs.getString("email");
birth=rs.getInt("birth");
System.out.println(id+" "+name+" "+email+" "+birth);
}
rs.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
public static class User {
private String user;
private String password;
public User() {
}
public User(String user, String password) {
super();
this.user = user;
this.password = password;
}
private static void register() {
try {
Connection conn = null;
try {
conn = getConnection();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Scanner in=new Scanner(System.in);
System.out.println("请输入你的姓名:");
String name=in.nextLine();
System.out.println("请输入你的登陆密码:");
String p1=in.next();
System.out.println("请再次输入你的登陆密码:");
String p2=in.next();
if(p1.equals(p2)) {
String password=p1;
String sql="insert into user(name,password) values(?,?)";
PreparedStatement pt = conn.prepareStatement(sql);
pt.setString(1,name);
pt.setString(2,password);
pt.execute();
System.out.println("注册成功!\n请登录:");
}
else {
System.out.println("你输入的密码与确认密码不相同，请重新注册:");
register();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static void login() {
try {
Connection conn = null;
try {
conn = getConnection();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Scanner in=new Scanner(System.in);
System.out.println("请输入你的姓名:");
String name=in.nextLine();
System.out.println("请输入你的密码:");
String password=in.next();
String sql = "select name,password from user where name=? and password=?";
PreparedStatement pt = conn.prepareStatement(sql);
pt.setString(1, name);
pt.setString(2, password);
ResultSet rs = pt.executeQuery();
//从给出的账号密码来检测是否有相同的账号密码
if(rs.next()) {
System.out.println("登陆成功!");
}else {
System.out.println("姓名或密码错误!\n请重新登陆:");
login();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public String toString() {
return "User [user=" + user + ", password=" + password + "]";
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
//连接数据库
public static Connection getConnection() throws Exception
{
//读取配置文件中的四个基本信息
InputStream is = Connection.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String diverClass = pros.getProperty("diverClass");
//加载驱动
Class.forName(diverClass);
//获取连接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
return conn;
}
//关闭连接和Statement的操作
public static void closeResource(Connection conn,Statement ps)
{
try {
if(ps != null)
ps.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
try
{
if(conn != null)
conn.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//关闭资源的操作
public static void closeResource(Connection conn,Statement ps,ResultSet rs)
{
try {
if(ps != null)
ps.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(rs != null)
rs.close();
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static class update {
public static int update(String sql, Object... args) {// 动态参数或者是可变参数的意思。
Connection con = null;
PreparedStatement pr = null;
try {
con = getConnection();
pr = con.prepareStatement(sql);
for (int i = 1; i <= args.length; i++) {
pr.setObject(i, args[i - 1]);
}
/*
* pr.execute(); 如果执行的是查询操作，有返回结果则返回ture 如果执行的是增删改，没有返回结果的，此方法返回false
*/
// pr.execute();
return pr.executeUpdate();// 执行了则返回影响的行数，失败则返回0，返回值为int类型
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
closeResource(con, pr);
}
return 0;
}
public <T> T find1(Class<T> clazz,String sql,Object ...args) {
Connection con = null;
PreparedStatement pr=null;
ResultSet re=null;
try {
//获取连接
con = getConnection();
//预编译处理sql语句
pr = con.prepareStatement(sql);
//填充占位符
for(int i=1;i<=args.length;i++) {
pr.setObject(i, args[i-1]);
}
//接受结果
re = pr.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = re.getMetaData();
//通过元数据获取结果集的列数
int count = rsmd.getColumnCount();
if(re.next()) {
T t = clazz.newInstance();
for(int i=0;i<count;i++) {
//获取每一列的值
Object value = re.getObject(i+1);
//获取每个列的列名
String columnName = rsmd.getColumnName(i+1);
//给t对象指定的某个属性值，依次赋值为value
//得到属性
Field field = clazz.getDeclaredField(columnName);
//保证可以访问，防止是私有属性不可以访问
field.setAccessible(true);
field.set(t, value);
}
return t;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
closeResource(con, pr, re);
}
return null;
}
public <T> List<T> find2(Class<T> clazz,String sql,Object ...args){
Connection con = null;
PreparedStatement pr=null;
ResultSet re=null;
try {
//获取连接
con = getConnection();
//预编译处理sql语句
pr = con.prepareStatement(sql);
//填充占位符
for(int i=1;i<=args.length;i++) {
pr.setObject(i, args[i-1]);
}
//接受结果
re = pr.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = re.getMetaData();
//通过元数据获取结果集的列数
int count = rsmd.getColumnCount();
//创建集合
ArrayList<T> list = new ArrayList<T>();
while(re.next()) {//if改为while
T t = clazz.newInstance();//空的构造器
for(int i=0;i<count;i++) {
//获取每一列的值
Object value = re.getObject(i+1);
//获取每个列的列名
String columnName = rsmd.getColumnName(i+1);
//给t对象指定的某个属性值，依次赋值为value
//得到属性
Field field = clazz.getDeclaredField(columnName);
//保证可以访问，防止是私有属性不可以访问
field.setAccessible(true);
field.set(t, value);
}
list.add(t);//将对象添加到数组中
}
return list;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
closeResource(con, pr, re);
}
return null;
}
}
}
案例八
JDBCUtil.java
package demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtil {
private static final String connectionurl="jdbc:mysql://localhost:3306/web01?serverTimezone=GMT";
private static final String username="root";
private static final String password="0721";
public static Connection getConnection() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
return DriverManager.getConnection(connectionurl, username, password);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public static void close(ResultSet rs,Statement stmt,Connection con) {
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(stmt!=null)
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(con!=null)
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
demo_main.java
package demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class demo_main {
public static void denglu(String id,String mima) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/web01?serverTimezone=GMT";
String user="root";
String password="0721";
Connection con=DriverManager.getConnection(url, user, password);
Statement stmt=con.createStatement();
String sql="select *from guanliyuan where id= '"+id+"' and mima='"+mima+"'";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()) {
System.out.println("登陆成功");
}
else {
System.out.println("未查询到该用户，请选择是否注册：1.是 2.否");
Scanner w=new Scanner(System.in);
int choice=w.nextInt();
switch(choice) {
case 1:
System.out.println("请输入添加的管理员账号、密码：");
String sid=w.next();
String smima=w.next();
add2(sid,smima);
break;
case 2:
System.out.println("欢迎下次使用！");
System.exit(0);
}
}
}
catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void add2(String id,String mima) {
Connection cos=null;
PreparedStatement stm=null;
ResultSet rc=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/web01?serverTimezone=GMT";
String user="root";
String password="0721";
cos=DriverManager.getConnection(url, user, password);
String sql="insert into guanliyuan(id,mima) value(?,?)";
stm=cos.prepareStatement(sql);
stm.setString(1, id);
stm.setString(2, mima);
int result=stm.executeUpdate();
if(result>0) {
System.out.println("注册成功");
menu();
}else {
System.out.println("注册失败");
System.exit(0);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
if(cos!=null)
try {
cos.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(stm!=null)
try {
stm.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(rc!=null)
try {
rc.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void menu() {
System.out.println(" =========欢迎使用用户信息管理系统========="+"\n");
Scanner s = new Scanner(System.in);
System.out.println("请先登录或注册：1.登录 2.注册");
int cho=s.nextInt();
switch(cho) {
case 1:
System.out.println("请输入管理员账号");
String id=s.next();
System.out.println("请输入密码");
String mima=s.next();
denglu(id,mima);
break;
case 2:
System.out.println("请输入添加的管理员账号、密码：");
String sid=s.next();
String smima=s.next();
add2(sid,smima);
break;
}
System.out.println("1.添加用户 2.删除用户 3.查询 4.修改密码 5.展示所有信息 6.按密码排序 7.分页查询 8.退出 ");
int num = s.nextInt();
switch(num) {
case 1:
System.out.println("请输入添加的用户姓名、密码：");
String user = s.next();
String pass=s.next();
add(user,pass);
break;
case 2:
System.out.println("请输入要删除的用户ID：");
int idss=s.nextInt();
delete(idss);
break;
case 3:
System.out.println("请输入查询的用户姓名、密码：");
String cname=s.next();
String cpassword=s.next();
find(cname,cpassword);
break;
case 4:
System.out.println("请输入要修改密码用户的ID和新密码：");
int ids=s.nextInt();
String newpass=s.next();
change(ids,newpass);
break;
case 5:
all();
break;
case 6:
paixu();
break;
case 7:
System.out.println("请输入要查询第几页以及每页数据条数：");
int number=s.nextInt();
int count=s.nextInt();
page(number,count);
break;
case 8:
System.out.println("谢谢您的使用");
System.exit(0);
}
}
public static void main(String[] args) {
menu();
}
public static void all() {
Connection con=null;
ResultSet rs=null;
Statement stmt=null;
try {
con=JDBC.getConnection();
stmt=con.createStatement();
rs= stmt.executeQuery("select * from user");
while(rs.next()) {
System.out.println(rs.getInt("ID")+" "+rs.getString("username")+" "+rs.getString("password"));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBC.close(rs, stmt, con);
}
}
public static boolean find(String username,String password) {
Connection con=null;
Statement stmt=null;
ResultSet rs=null;
try {
con=JDBC.getConnection();
stmt=con.createStatement();
String sql="select *from user where username= '"+username+"' and password='"+password+"'";
rs=stmt.executeQuery(sql);
if(rs.next()) {
System.out.println(rs.getInt("ID")+" "+rs.getString("username")+" "+rs.getString("password"));
return true;
}else {
System.out.println("未查询到该用户");
return false;
}
}catch(Exception e) {
e.printStackTrace();
}
finally {
JDBC.close(rs, stmt, con);
}
return false;
}
//分页查询
public static void page(int pagenumber,int pagecount) {
Connection con=null;
ResultSet rs=null;
PreparedStatement stmt=null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/web01?serverTimezone=GMT";
String user="root";
String password="0721";
con=DriverManager.getConnection(url, user, password);
stmt=con.prepareStatement("select * from user limit ?,?");
stmt.setInt(1,(pagenumber-1)*pagecount);
stmt.setInt(2, pagecount);
rs=stmt.executeQuery();
while(rs.next()) {
System.out.println(rs.getInt("ID")+" "+rs.getString("username")+" "+rs.getString("password"));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBC.close(rs, stmt, con);
}
}
//添加
public static void add(String username,String password) {
Connection con=null;
ResultSet rs=null;
PreparedStatement stmt=null;
try {
con=JDBC.getConnection();
String sql="insert into user(username,password) value(?,?)";
stmt=con.prepareStatement(sql);
stmt.setString(1, username);
stmt.setString(2, password);
int result=stmt.executeUpdate();
if(result>0) {
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBC.close(rs, stmt, con);
}
}
//删除
public static void delete(int id) {
Connection con=null;
ResultSet rs=null;
PreparedStatement stmt=null;
try {
con=JDBC.getConnection();
String sql="delete from user where id=?";
stmt=con.prepareStatement(sql);
stmt.setInt(1, id);
int result=stmt.executeUpdate();
if(result>0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBC.close(rs, stmt, con);
}
}
//修改
public static void change(int id,String newpassword) {
Connection con=null;
ResultSet rs=null;
PreparedStatement stmt=null;
try {
con=JDBC.getConnection();
String sql="update user set password=? where id=?";
stmt=con.prepareStatement(sql);
stmt.setString(1, newpassword);
stmt.setInt(2, id);
int result=stmt.executeUpdate();
if(result>0) {
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBC.close(rs, stmt, con);
}
}
public static void paixu() {
Connection con=null;
ResultSet rs=null;
Statement stmt=null;
try {
con=JDBC.getConnection();
stmt=con.createStatement();
rs= stmt.executeQuery("SELECT * from user ORDER BY password ASC");
while(rs.next()) {
System.out.println(rs.getInt("ID")+" "+rs.getString("username")+" "+rs.getString("password"));
}
}
catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBC.close(rs, stmt, con);
}
}
}
885
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
