网站建设备案查询,网站建设 中企动力东莞后台管理,南宁网站建设哪家专业,整合营销方案案例文章目录项目文件结构项目运行效果项目详细代码项目文件下载相关案例案例一案例二案例三案例四案例五案例六案例七案例八项目文件结构 项目运行效果 项目详细代码
JDBCUtils.java
package jdbc;import java.sql.*;
import com.mysql.jdbc.Driver;public class JDBCUtils {pri…
文章目录项目文件结构项目运行效果项目详细代码项目文件下载相关案例案例一案例二案例三案例四案例五案例六案例七案例八项目文件结构 项目运行效果 项目详细代码
JDBCUtils.java
package jdbc;import java.sql.*;
import com.mysql.jdbc.Driver;public class JDBCUtils {private static final String connectionURLjdbc:mysql://localhost:3306/books?useSSLfalseuseUnicodetruecharacterEncodingUTF8;private static final String usernameroot;private static final String password1234;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 blocke.printStackTrace();}try {if(conn!null)conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 inputnew Scanner(System.in);int num0;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 flagtrue;Scanner innew Scanner(System.in); do{ System.out.println(请输入登陆账号); String username in.next(); System.out.println(请输入密码); String password in.next();for(int i0;iUsername.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(flagtrue){ 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 connull;Statement stmtnull;ResultSet rsnull;try {conJDBCUtils.getConnection();stmtcon.createStatement();rsstmt.executeQuery(select * from book,bookmagic where book.bnobookmagic.bno);System.out.println( 编号 书籍名 主人 页码 价格 作者 );while(rs.next()){int bnors.getInt(bno);String namers.getString(name);String ownerrs.getString(owner);int pagers.getInt(page);int pricers.getInt(price);String articlers.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 connull; PreparedStatement stmtnull;ResultSet rsnull;try {conJDBCUtils.getConnection();stmtcon.prepareStatement(select* from book limit ?,?);//mysql中 使用limit查询的参数第一个为 起始记录数; 第二个为 每页的数量stmt.setInt(1, (pageNumber-1)*pageCount);stmt.setInt(2, pageCount);rsstmt.executeQuery();while(rs.next()){int bnors.getInt(bno);String namers.getString(name);String ownerrs.getString(owner);int pagers.getInt(page);int pricers.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 connull;PreparedStatement stmtnull;ResultSet rsnull;try {conJDBCUtils.getConnection();String sqlinsert into book values(?,?,?,?,?);stmtcon.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 connull;PreparedStatement stmtnull;ResultSet rsnull;try {conJDBCUtils.getConnection();String sqldelete from book where price ?;stmtcon.prepareStatement(sql);stmt.setInt(1, price);;int result stmt.executeUpdate();//返回值代表收到影响的行列if(result0){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 connull;PreparedStatement stmtnull;ResultSet rsnull;try {conJDBCUtils.getConnection();String sqlupdate book set price?,name?,owner?,page? where bno? ;stmtcon.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(result0){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 tnew Register();t.loginfirst();t.loginsecond();Test tenew 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 prices1input.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 psnull;try {Class.forName(com.mysql.cj.jdbc.Driver);con DriverManager.getConnection(jdbc:mysql://localhost:3306/sys?useSSLfalseserverTimezoneUTC,root,zck12121);Scanner anew Scanner(System.in);while(true){System.out.print(输入学生姓名);String namea.next();System.out.print(输入学生性别);String sexa.next();System.out.print(输入学生地址);String sitea.next();System.out.print(输入学生专业);String majora.next();System.out.print(输入学生专业成绩);Float maiorgradea.nextFloat();String sqlinsert 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 nono;String choosea.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?useSSLfalseserverTimezoneUTC,root,zck12121);sta con.createStatement();String sqlselect * from student;rssta.executeQuery(sql);//执行sql语句System.out.println(学生姓名\t学生性别\t学生地址\t学生专业\t学生专业成绩);while(rs.next()){System.out.println(rs.getString(1)\trs.getString(2)\trs.getString(3)\trs.getString(4)\trs.getString(5));}}public static void delete() throws Exception //删除{Connection con null;Statement sta null;ResultSet rs null;PreparedStatement psnull;Class.forName(com.mysql.cj.jdbc.Driver);con DriverManager.getConnection(jdbc:mysql://localhost:3306/sys?useSSLfalseserverTimezoneUTC,root,zck12121);boolean ntrue;int y0;while(n){System.out.print(请输入要删除的学生姓名);Scanner anew Scanner(System.in);String namea.next();sta con.createStatement();String sqlselect * from student,Sql;rssta.executeQuery(sql);//执行sql语句while(rs.next()){int mname.compareTo(rs.getString(1));if(m0){y1;}}if(y0){System.out.println(该用户名不存在请重新输入);continue;}Sqldelete from student where name ? ;ps con.prepareStatement(Sql);ps.setObject(1, name);ps.execute();nfalse;System.out.println(删除成功);}}public static void updata() throws Exception //更新{Connection con null;Statement sta null;ResultSet rs null;PreparedStatement psnull;Class.forName(com.mysql.cj.jdbc.Driver);con DriverManager.getConnection(jdbc:mysql://localhost:3306/sys?useSSLfalseserverTimezoneUTC,root,zck12121);boolean ntrue;int y0;while(n){System.out.print(请输入要更改的学生姓名);Scanner anew Scanner(System.in);String namea.next();sta con.createStatement();String sqlselect * from student;rssta.executeQuery(sql);//执行sql语句while(rs.next()){int mname.compareTo(rs.getString(1));if(m0){y1;}}if(y0){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 choosea.nextInt();String Sql;if(choose1){System.out.print(将姓名修改为);String newsa.next();Sqlupdate student 9 as 9 set name ? where name ? ;ps con.prepareStatement(Sql);ps.setObject(1, news);ps.setObject(2, name);ps.execute();nfalse;System.out.println(修改成功);}else if(choose2){System.out.print(将性别修改为);String newsa.next();Sqlupdate student set sex? where name?;ps con.prepareStatement(Sql);ps.setObject(1, news);ps.setObject(2, name);ps.execute();nfalse;System.out.println(修改成功);}else if(choose3){System.out.print(将地址修改为);String newsa.next();Sqlupdate student set site? where name?;ps con.prepareStatement(Sql);ps.setObject(1, news);ps.setObject(2, name);ps.execute();nfalse;System.out.println(修改成功);}else if(choose4){System.out.print(将专业修改为);String newsa.next();Sqlupdate student set major? where name?;ps con.prepareStatement(Sql);ps.setObject(1, news);ps.setObject(2, name);ps.execute();nfalse;System.out.println(修改成功);}else if(choose5){System.out.print(将专业成绩修改为);float newsa.nextFloat();Sqlupdate student set majorgrade? where name?;ps con.prepareStatement(Sql);ps.setObject(1, news);ps.setObject(2, name);ps.execute();nfalse;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?useSSLfalseserverTimezoneUTC,root,zck12121);sta con.createStatement();System.out.println(1.查看全部学生信息);System.out.println(2.查看前三名学生信息);String Sqlnull;Scanner anew Scanner(System.in);int choosea.nextInt();if(choose1)Sqlselect * from student ORDER BY majorgrade DESC;if(choose2)Sqlselect * from student order by majorgrade asc limit 0,3;rssta.executeQuery(Sql);//执行sql语句System.out.println(学生姓名\t学生性别\t学生专业成绩);while(rs.next()){System.out.println(rs.getString(1)\trs.getString(2)\trs.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 anew Scanner(System.in);clear();switch(choosea.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?useSSLfalseserverTimezoneUTC,root,zck12121);sta con.createStatement();String sqlselect * from register;rssta.executeQuery(sql);//执行sql语句while(rs.next()){int ausername.compareTo(rs.getString(1));int bpassword.compareTo(rs.getString(2));if(a0b0)return 1;else if(a0b!0)return 2;}return 0;}public static void enroll() throws Exception //登录{Scanner snew Scanner(System.in);System.out.print(请输入账号);String users.next();System.out.print(请输入密码);String words.next();int wget(user,word);if(w1){System.out.println(登录成功);jumpshort();go();}else if(w2)System.out.println(密码有误请重新输入);elseSystem.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?useSSLfalseserverTimezoneUTC,root,zck12121);sta con.createStatement();String sqlselect * from register;rssta.executeQuery(sql);//执行sql语句while(rs.next()){int aname1.compareTo(rs.getString(1));if(a0){System.out.println(该用户名已存在请重新输入);return true;}}return false;}public static void logon() throws Exception //注册{Connection con null;Statement sta null;PreparedStatement psnull;try {Class.forName(com.mysql.cj.jdbc.Driver);con DriverManager.getConnection(jdbc:mysql://localhost:3306/sys?useSSLfalseserverTimezoneUTC,root,zck12121);Scanner anew Scanner(System.in);boolean ntrue;String newname null;while(n){System.out.print(请输入您的ID);newnamea.next();ncheck1(newname);}System.out.print(请输入您的密码);String newworda.next();String sqlinsert 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 anew Scanner(System.in);int choosea.nextInt();if(choose1)enroll();else if(choose2)logon();elseSystem.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 connull;try {//加载驱动程序Class.forName(com.mysql.jdbc.Driver);//连接MySQLcon DriverManager.getConnection(jdbc:mysql://localhost:3306/demo02?serverTimezoneGMT,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 numsc.nextInt();sc.nextLine();if(num1){System.out.println(请输入用户名 );String asc.nextLine(); System.out.println(请输入密码 );String bsc.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 xsc.nextInt();sc.nextLine();if(x1){System.out.println(请输入用户名 );String csc.nextLine();if(a.equals(c)){System.out.println(请输入密码 );String dsc.nextLine();if(b.equals(d)){System.out.println(密码正确);}else{System.out.println(密码错误);System.out.println(请重新输入密码 );dsc.nextLine();if(b.equals(d)){System.out.println(密码正确);}else{System.out.println(密码再次错误 即将离开该页面);System.exit(0); }}}else{System.out.println(用户名输入错误 已离开该页面);System.out.println(---------------);menu(); } }if(x0){System.out.println(成功返回); System.out.println(---------------);menu();}break; } }else if(num2){System.out.println(尚未注册 无法登录);System.out.println(如想登陆 请前往注册);System.out.println(---------------);register();}else if(num0){System.out.println(成功返回);System.out.println(---------------);menu();}return ;}//主菜单public static void menu() {TheSqlconnection();Scanner smnew 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 msm.nextInt();if(m1) {menu1();}else if(m2) {menu2();}else if(m0) {System.out.println(------------------);System.out.println(--已退出 请勿操作--);System.out.println(------------------);System.exit(0);}else {System.out.println(----输入错误 将重复此操作);menu();}}//管理员菜单public static void menu1() {register();for(int i0;i1000;i) {Scanner scnew 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 xsc.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 b0;b500;b) {Scanner ccnew 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 tcc.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 sqlinsert into student value(?,?,?,?);Connection con TheSqlconnection();Scanner ccnew Scanner(System.in);try {System.out.println(------------------------);System.out.println(请输入你要添加的学生个数);System.out.println(------------------------);int ncc.nextInt();for(int p1;pn;p) {System.out.println(请输入学生学号);int idcc.nextInt();System.out.println(请输入学生姓名);cc.nextLine();String namecc.nextLine();System.out.println(请输入学生性别);String sexcc.nextLine();System.out.println(请输入学生成绩);int gradecc.nextInt();PreparedStatement ps con.prepareStatement(sql);//设置SQL语句里的参数ps.setInt(1,id);ps.setString(2,name);ps.setString(3,sex);ps.setInt(4,grade);int rowsps.executeUpdate();if(rows0) {System.out.println(第p个学生信息添加成功);System.out.println();}}}catch(SQLException e) {e.printStackTrace(); } }//删除private static void del() {Connection con TheSqlconnection();Scanner ccnew Scanner(System.in); try { System.out.println(请输入想要删除的学生学号);int idcc.nextInt();String sqldelete from student where idid;PreparedStatement ps con.prepareStatement(sql);int rowsps.executeUpdate();if(rows0) {System.out.println(删除学生信息成功);System.out.println();}else {System.out.println(删除学生信息失败);}}catch(SQLException e) {e.printStackTrace(); } }//查询private static void search() {Connection con TheSqlconnection();Scanner ccnew Scanner(System.in); try { String sqlselect * from student where id?; PreparedStatement ps con.prepareStatement(sql); System.out.println(请输入想要查询的学生学号);int idcc.nextInt();ps.setInt(1,id); ResultSet rs ps.executeQuery();if(rs.next()) {String namers.getString(name);String sexrs.getString(sex);int graders.getInt(grade); System.out.println(查询结果如下);System.out.println();System.out.println(id\t姓名\t性别\t成绩); System.out.println(id\tname\tsex\tgrade);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 ccnew Scanner(System.in); try { System.out.println(请输入想要更新的学生学号);int idcc.nextInt();System.out.println(请输入想要更新的学生姓名);cc.nextLine();String namecc.nextLine();System.out.println(请输入想要更新的学生性别);String sexcc.nextLine();System.out.println(请输入想要更新的学生成绩);int gradecc.nextInt();String sqlupdate 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 ccnew Scanner(System.in);Connection con TheSqlconnection(); System.out.println(每页显示五条记录 请问你想查询哪一页(请输入大于等于1的整数));int numcc.nextInt(); int start0;int count5;try {Statement ps con.createStatement(); if (num1) {start 0; }else{if (num1) { 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\tname\tsex\tgrade); } }catch(SQLException e){ e.printStackTrace(); } }//分组排序private static void order() {Scanner ccnew Scanner(System.in);Connection con TheSqlconnection();System.out.println();System.out.println( 1.升序 ); System.out.println( 2.降序 ); System.out.println(); System.out.println(请选择排序的方式);int acc.nextInt();if(a1) {try { String sqlselect * 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(a2) {try {String sqlselect * 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));urlpro.getProperty(url);usernamepro.getProperty(username);passwordpro.getProperty(password);driverpro.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?useSSLfalseserverTimezoneUTC ;
// 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;Overridepublic 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:flagfalse;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(注册成功);} elseSystem.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 rsnull;String sql select *from websites where id?;try {conn JDBCUtils.getconnection();pstmt conn.prepareStatement(sql);System.out.println(请输入要查询的ID);pstmt.setString(1, sc.next());rspstmt.executeQuery();while (rs.next()){int idrs.getInt(id);String name rs.getString(name) ;String pass rs.getString(url) ;String countryrs.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 rsnull;String sql select country,SUM(alexa) FROM websites group by country order by SUM(alexa) desc ;try {conn JDBCUtils.getconnection();pstmt conn.prepareStatement(sql);rspstmt.executeQuery();while (rs.next()){String countryrs.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 page1;static void limit(){Scanner sc new Scanner(System.in);Connection conn null;PreparedStatement pstmt null;ResultSet rsnull;String sqlselect *from websites limit ?,?;try {System.out.println(请输入每页显示条数);int sizesc.nextInt();conn JDBCUtils.getconnection();pstmt conn.prepareStatement(sql);// System.out.println(请输入起始号);pstmt.setInt(1,(page-1)*size);pstmt.setInt(2,size);rspstmt.executeQuery();while (rs.next()){int idrs.getInt(id);String name rs.getString(name) ;String pass rs.getString(url) ;String countryrs.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 TransferSource Server : localhost_3306Source Server Type : MySQLSource Server Version : 80019Source Host : localhost:3306Source Schema : runnoobTarget Server Type : MySQLTarget Server Version : 80019File Encoding : 65001Date: 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 TransferSource Server : localhost_3306Source Server Type : MySQLSource Server Version : 80019Source Host : localhost:3306Source Schema : runnoobTarget Server Type : MySQLTarget Server Version : 80019File Encoding : 65001Date: 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?useSSLfalseserverTimezoneUTC
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 scnew Scanner(System.in);//注册public static void login() {System.out.println();String urljdbc:mysql://localhost:3306/jt_db?useUnicodetruecharacterEncodingUTF8useSSLfalse;String userroot;String password123456;Scanner scnew Scanner(System.in);System.out.print(请输入你的注册用户名);String unamesc.next();System.out.print(请输入你的注册密码);String upwdsc.next();String sqlinsert into login (uname,upwd) values (?,?);Connection connull;PreparedStatement psnull;try {Class.forName(com.mysql.jdbc.Driver);conDriverManager.getConnection(url, user, password);pscon.prepareStatement(sql);ps.setString(1, uname);ps.setString(2,upwd);int rowps.executeUpdate();if(row0) {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 connull;PreparedStatement psnull;ResultSet rsnull;Scanner scnew Scanner(System.in);System.out.print(请输入你的登陆用户名);String unamesc.next();System.out.print(请输入你的登陆密码);String upwdsc.next();try {//注册驱动并获取连接Class.forName(com.mysql.jdbc.Driver);//使用什么驱动连接数据库String urljdbc:mysql://localhost:3306/jt_db?useUnicodetruecharacterEncodingUTF8useSSLfalse;String userroot;String password123456;conDriverManager.getConnection(url, user, password);String sqlselect count(*) from login where uname? and upwd?;pscon.prepareStatement(sql);ps.setString(1, uname);ps.setString(2,upwd);rsps.executeQuery();if(rs.next()) {int countrs.getInt(1);if(count0) {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 connull;Statement stmtnull;ResultSet rsnull;try {//注册驱动并获取连接Class.forName(com.mysql.jdbc.Driver);//使用什么驱动连接数据库String urljdbc:mysql://localhost:3306/jt_db?useUnicodetruecharacterEncodingUTF8useSSLfalse;String userroot;String password123456;conDriverManager.getConnection(url, user, password);stmtcon.createStatement();rsstmt.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 idsc.nextInt();String namesc.next();String gendersc.next();double scoresc.nextDouble();Connection connull;PreparedStatement psnull;ResultSet rsnull;try {//注册驱动并获取连接Class.forName(com.mysql.jdbc.Driver);//使用什么驱动连接数据库String urljdbc:mysql://localhost:3306/jt_db?useUnicodetruecharacterEncodingUTF8useSSLfalse;String userroot;String password123456;conDriverManager.getConnection(url, user, password);//获取传输器执行SQL语句String sqlinsert into stu value(?,?,?,?);pscon.prepareStatement(sql);//设置SQl语句中的参数ps.setInt(1,id);ps.setString(2, name);ps.setString(3,gender);ps.setDouble(4, score);//执行sql语句int rowsps.executeUpdate();//不要二次传入SQLif(rows0) {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 idsc.nextInt();String namesc.next();String gendersc.next();double scoresc.nextDouble();Connection connull;PreparedStatement psnull;ResultSet rsnull;try {//注册驱动并获取连接Class.forName(com.mysql.jdbc.Driver);//使用什么驱动连接数据库String urljdbc:mysql://localhost:3306/jt_db?useUnicodetruecharacterEncodingUTF8useSSLfalse;String userroot;String password123456;conDriverManager.getConnection(url, user, password);//获取传输器执行SQL语句String sqlupdate stu set name?,gender?,score? where id?;pscon.prepareStatement(sql);//设置SQL语句中的参数ps.setString(1, name);ps.setString(2,gender);ps.setDouble(3, score);ps.setInt(4,id);//执行SQl语句int rowsps.executeUpdate();if(rows0) {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 idsc.nextInt();Connection connull;PreparedStatement psnull;ResultSet rsnull;try {//注册驱动并获取连接Class.forName(com.mysql.jdbc.Driver);//使用什么驱动连接数据库String urljdbc:mysql://localhost:3306/jt_db?useUnicodetruecharacterEncodingUTF8useSSLfalse;String userroot;String password123456;conDriverManager.getConnection(url, user, password);//获取传输器执行SQL语句String sqldelete from stu where id?;pscon.prepareStatement(sql);//设置SQl语句的参数ps.setInt(1, id);//执行SQL语句int rowsps.executeUpdate();if(rows0) {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 connull;Statement stmtnull;ResultSet rsnull;try {//注册驱动并获取连接Class.forName(com.mysql.jdbc.Driver);//使用什么驱动连接数据库String urljdbc:mysql://localhost:3306/jt_db?useUnicodetruecharacterEncodingUTF8useSSLfalse;String userroot;String password123456;conDriverManager.getConnection(url, user, password);//获取传输器执行SQL语句stmtcon.createStatement();rsstmt.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 connull;Statement stmtnull;ResultSet rsnull;try {//注册驱动并获取连接Class.forName(com.mysql.jdbc.Driver);//使用什么驱动连接数据库String urljdbc:mysql://localhost:3306/jt_db?useUnicodetruecharacterEncodingUTF8useSSLfalse;String userroot;String password123456;conDriverManager.getConnection(url, user, password);stmtcon.createStatement();rsstmt.executeQuery(select * from stu where id100 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 stubScanner innew Scanner(System.in);System.out.println(********用户界面********);System.out.println(请选择\n 1:用户登录\n 2:用户注册);System.out.println(**********************);int iin.nextInt();switch(i) {case 1:login();break;case 2:register();break;default:System.out.println(输入有误!);System.exit(0);}int j;for(j0;j1000;j) {menu();System.out.println(请输入你想实现的功能所对应的序号:);int ain.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 sqlselect * from students;Connection conn SqlConnection();try {Statement aconn.createStatement();ResultSet rsa.executeQuery(sql);System.out.println(————————————————————————————————);System.out.println(数据库显示内容如下:);System.out.println(————————————————————————————————);System.out.println(id 姓名 性别 成绩);System.out.println(———————————————————————————————);Integer id null;String namenull;String ssexnull;Integer gradenull;while(rs.next()) {idrs.getInt(id);namers.getString(name);ssexrs.getString(ssex);graders.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 sqlinsert into students values(?,?,?,?);Connection conn SqlConnection();Scanner innew Scanner(System.in);try {System.out.println(————————————————————————————);System.out.println(请输入你要添加的学生个数:);System.out.println(—————————————————————————————);int jin.nextInt();for(int i0;ij;i) {System.out.println(请输入学生学号:);int idin.nextInt();System.out.println(请输入学生姓名:);in.nextLine();String namein.nextLine();System.out.println(请输入学生性别:);String ssexin.nextLine();System.out.println(请输入学生成绩:);int gradein.nextInt();PreparedStatement ps conn.prepareStatement(sql);ps.setInt(1, id);ps.setString(2, name);ps.setString(3,ssex);ps.setInt(4, grade);int rowsps.executeUpdate();if(rows0) {System.out.println(添加成功!);}}}catch(SQLException e) {e.printStackTrace();}}public static void delete() {String sqldelete from students where id?;Scanner innew Scanner(System.in);Connection conn SqlConnection();try {PreparedStatement ps conn.prepareStatement(sql);System.out.println(——————————————————————————————————);System.out.println(请输入你想要删除的学生的学号:);int idin.nextInt();ps.setInt(1, id);int aps.executeUpdate();if(a0) {System.out.println(删除成功!);}}catch(SQLException e) {e.printStackTrace();System.out.println(删除失败!);}}public static void select() throws SQLException{Connection conn SqlConnection();Scanner innew Scanner(System.in);String sqlselect name,ssex,grade from students where id?;PreparedStatement ps conn.prepareStatement(sql);System.out.println(请输入你想要查询的学生所对应的学号:);int idin.nextInt();ps.setInt(1,id);ResultSet rsps.executeQuery();while(rs.next()) {String namers.getString(name);String ssexrs.getString(ssex);int graders.getInt(grade);System.out.println(name ssex grade);}rs.close();ps.close();conn.close();}public static void update() {System.out.println(请输入要更新的学生学号:);Scanner innew Scanner(System.in);int idin.nextInt();System.out.println(请输入要更新的学生姓名:);String namein.next();System.out.println(请输入要更新的学生性别:);String ssexin.next();System.out.println(请输入要更新的学生成绩:);int gradein.nextInt();try {Connection connSqlConnection();String sqlupdate students set name?,ssex?,grade? where id?;PreparedStatement psconn.prepareStatement(sql);ps.setString(1, name);ps.setString(2,ssex);ps.setInt(3,grade);ps.setInt(4, id);int aps.executeUpdate();if(a0) {System.out.println(学生信息修改成功!);}}catch(SQLException e) {e.getStackTrace();}}private static void select_1() {String sqlselect * from students group by id,ssex order by grade desc;;Connection conn SqlConnection();try {PreparedStatement psconn.prepareStatement(sql);Statement aconn.createStatement();ResultSet rsa.executeQuery(sql);System.out.println(————————————————————————————————);System.out.println(数据库显示内容如下:);System.out.println(————————————————————————————————);System.out.println(id 姓名 性别 成绩);System.out.println(———————————————————————————————);Integer id null;String namenull;String ssexnull;Integer gradenull;ps.execute();while(rs.next()) {idrs.getInt(id);namers.getString(name);ssexrs.getString(ssex);graders.getInt(grade);System.out.println(id name ssex grade);}rs.close();conn.close();}catch(SQLException e) {e.printStackTrace();}}private static void select_2() {String sqlselect * from students group by id,ssex order by grade asc;;Connection conn SqlConnection();try {Statement aconn.createStatement();ResultSet rsa.executeQuery(sql);PreparedStatement psconn.prepareStatement(sql);System.out.println(————————————————————————————————);System.out.println(数据库显示内容如下:);System.out.println(————————————————————————————————);System.out.println(id 姓名 性别 成绩);System.out.println(———————————————————————————————);Integer id null;String namenull;String ssexnull;Integer gradenull;ps.execute();while(rs.next()) {idrs.getInt(id);namers.getString(name);ssexrs.getString(ssex);graders.getInt(grade);System.out.println(id name ssex grade);}rs.close();conn.close();}catch(SQLException e) {e.printStackTrace();}}private static void select_3() {Scanner innew Scanner(System.in);Connection conn SqlConnection();System.out.println(每页显示六条记录请输入你想查询的页对应的序号:);int ain.nextInt();int b 0;int c6;try {Statement psconn.createStatement();if(a1) {b 0;}else {if(a1) {System.out.println(输入错误无法进行查询);}else {b(a-1)*6;}}String sqlselect * from students order by id asc limit b , c;ResultSet rsps.executeQuery(sql);System.out.println(学号 姓名 性别 成绩);System.out.println(————————————————————————————————————————);while(rs.next()) {int idrs.getInt(1);String namers.getString(2);String ssexrs.getString(3);int graders.getInt(4);System.out.println(id name ssex grade);}}catch(SQLException e) {e.printStackTrace();}}public static void register() throws SQLException{Connection conn SqlConnection();Scanner innew Scanner(System.in);System.out.println(请输入你的姓名:);String namein.nextLine();System.out.println(请输入你的登陆密码:);String p1in.next();System.out.println(请再次输入你的登陆密码:);String p2in.next();if(p1.equals(p2)) {String passwordp1;String sqlinsert 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 innew Scanner(System.in);System.out.println(请输入你的姓名:);String namein.nextLine();System.out.println(请输入你的密码:);String passwordin.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 connnull;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 statementconn.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 {drivercom.mysql.jdbc.Driver;urljdbc:mysql://localhost:3306/student;userroot;passwordyang1234; 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 innew Scanner(System.in);logan();boolean btrue;while(b){menu();int ain.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:bfalse;System.out.println(再见谢谢您的使用);break;default:System.out.println(选择错误请重新选择);break;} }}static void update() { //修改信息Connection connDBtool.open();try {SuppressWarnings(resource)Scanner innew Scanner(System.in); SuppressWarnings(resource)Scanner snew Scanner(System.in);String sql;String sno;String sname;String ssex;String sage; String sscore;System.out.println(请输入你那个被修改学生的学号);snos.nextLine();System.out.println(请选择你要修改的信息的选项);System.out.println(1.学号 2.姓名 3性别 4年龄 5.成绩);int ain.nextInt();switch(a) { case 1: System.out.println(请输入要修改的学号); String sno1s.nextLine(); sqlupdate student set Sno\sno1\where Sno\sno\;; PreparedStatement pst(PreparedStatement)conn.prepareStatement(sql);SuppressWarnings(unused) int ipst.executeUpdate();//System.out.println(sql);System.out.println(修改成功);break; case 2: System.out.println(请输入要修改的姓名); snames.nextLine(); sqlupdate student set Sname\sname\where Sno\sno\;; PreparedStatement ps(PreparedStatement)conn.prepareStatement(sql);SuppressWarnings(unused) int jps.executeUpdate();// System.out.println(j);System.out.println(修改成功);break;case 3:System.out.println(请输入要修改的性别); ssexs.nextLine(); sqlupdate student set Ssex\ssex\where Sno\sno\;; PreparedStatement p(PreparedStatement)conn.prepareStatement(sql);SuppressWarnings(unused) int kp.executeUpdate();// System.out.println(k);System.out.println(修改成功);break;case 4: System.out.println(请输入要修改的年龄); sages.nextLine(); sqlupdate student set Sage\sage\where Sno\sno\;; PreparedStatement t(PreparedStatement)conn.prepareStatement(sql);SuppressWarnings(unused) int lt.executeUpdate();// System.out.println(k);System.out.println(修改成功);break;case 5:System.out.println(请输入要修改的成绩); sscores.nextLine(); sqlupdate student set Stel\sscore\where Sno\sno\;; PreparedStatement r(PreparedStatement)conn.prepareStatement(sql);SuppressWarnings(unused) int mr.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 connDBtool.open();String sqlselect * from student;;try {Statement stmtconn.createStatement();ResultSet rsstmt.executeQuery(sql);System.out.println(学号-------------姓名---------------性别-----------年龄---------------成绩);while(rs.next()){String snors.getString(1);String snamers.getString(2);String ssexrs.getString(3);String sagers.getString(4);String sscorers.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 innew Scanner(System.in);System.out.println(请输入要删除的学生信息的学号);Connection connDBtool.open();String sqlin.nextLine();try {Statement stmtconn.createStatement();stmt.executeUpdate(delete from student where Snosql);System.out.println(删除成功);} catch (SQLException e) {e.printStackTrace();}finally {DBtool.close(conn);}}static void insert() { //增加信息Connection connDBtool.open();String snonull;String snamenull;String ssexnull;int sage0;String stelnull;SuppressWarnings(resource)Scanner innew Scanner(System.in);System.out.println(请输入添加学生的学号);snoin.nextLine();System.out.println(请输入添加学生的姓名);snamein.nextLine();System.out.println(请输入添加学生的性别);ssexin.nextLine(); System.out.println(请输入添加学生的年龄);SuppressWarnings(resource)Scanner anew Scanner(System.in);sagea.nextInt(); System.out.println(请输入添加学生的成绩);stelin.nextLine();try {String sqlinsert into student (Sno,Sname,Ssex,Sage,Sscore)values(?,?,?,?,?);PreparedStatement pstconn.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 ipst.executeUpdate();System.out.println(添加成功);} catch (SQLException e) {e.printStackTrace();}finally {DBtool.close(conn);}}static void query() { //查询信息SuppressWarnings(resource)Scanner innew Scanner(System.in);System.out.println(请输入你要查询学生的学号);String snoin.nextLine();String sqlselect * from student where Sno\sno\;; Connection connDBtool.open();try { Statement stmtconn.createStatement();ResultSet rsstmt.executeQuery(sql);System.out.println(学号-------------姓名---------------性别--------------年龄---------------成绩);while(rs.next()){snors.getString(1);String snamers.getString(2);String ssexrs.getString(3);String sagers.getString(4);String sscorers.getString(5);System.out.println(sno sname ssex sage sscore);}} catch (SQLException e) {e.printStackTrace();}finally {DBtool.close(conn);}}static void createtable() {Connection connDBtool.open();String sqlcreate table student(Sno char(10) primary key ,Sname varchar(20),Ssex char(4),Sage int,Sscore char(11));;try {Statement stmtconn.createStatement();stmt.execute(sql);} catch (SQLException e) {e.printStackTrace();}finally {DBtool.close(conn);}}static void logan() {SuppressWarnings(resource)Scanner inputnew Scanner(System.in);String account;String password;do {System.out.println(请输入账号和密码中间用空格间隔:);accountinput.next();passwordinput.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 connDBtool.open();try {Statement stmtconn.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 connDBtool.open();String sqlselect * from student limit 0,3;;try {Statement stmtconn.createStatement();ResultSet rsstmt.executeQuery(sql);System.out.println(学号-----姓名----------性别--------年龄--------成绩);while(rs.next()){String snors.getString(1);String snamers.getString(2);String ssexrs.getString(3);String sagers.getString(4);String sscorers.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;}Overridepublic String toString() {return Customer[idid,namename,emailemail,birthbirth];}public static void main(String[] args) {// TODO Auto-generated method stubScanner innew Scanner(System.in);System.out.println(----------用户界面---------------);System.out.println(----------1:用户登录-------------);System.out.println(----------2:用户注册-------------);System.out.println(----------3:退出------------------);System.out.println(---------------------------);int iin.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(j0;j1000;j) {menu();System.out.println(请输入所选功能的序号:);int ain.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 stubSystem.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;iargs.length;i)
// {
// ps.setObject(i1, args[i]);//参数声明会产生错误
// }
// //4.执行sql语句
// ps.execute();
// } catch (Exception e) {
// e.printStackTrace();
// }finally {
// //关闭资源
// JDBCUtils.closeResource(conn, ps);
// }
//
//
// }//增加数据private static void add() {// TODO Auto-generated method stubString sqlinsert 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 jin.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 rowsps.executeUpdate();if(rows0) {System.out.println(添加成功!);}}}catch(Exception e) {e.printStackTrace();}}//修改数据private static void update() {// TODO Auto-generated method stubString 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 stubString sqldelete 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 stubConnection 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 blocke.printStackTrace();}System.out.println(请输入你想要查询的学生所对应的学号:);int id in.nextInt();try {ps.setInt(1,id);} catch (Exception e) {// TODO Auto-generated catch blocke.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 blocke.printStackTrace();}try {rs.close();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}//分组查询学生信息(降序)private static void select_1() {// TODO Auto-generated method stubString sqlselect * from costomers order by email 1111126.com;;Connection conn null;try {//预编译sql语句Statement aconn.createStatement();//获取结果集的元数据ResultSet rsa.executeQuery(sql);System.out.println(------------------------);System.out.println(数据库中的内容显示);System.out.println(------------------------);System.out.println(学号 姓名 邮箱 生日);System.out.println( );Integer id null;String namenull;String emailnull;Integer birthnull;while(rs.next()) {idrs.getInt(id);namers.getString(name);emailrs.getString(email);birthrs.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 stubString sqlselect * from costomers order by email 3333126.com;;Connection conn null;try {//预编译sql语句Statement aconn.createStatement();//获取结果集的元数据ResultSet rsa.executeQuery(sql);System.out.println(------------------------);System.out.println(数据库中的内容显示);System.out.println(------------------------);System.out.println(学号 姓名 邮箱 生日);System.out.println( );Integer id null;String namenull;String emailnull;Integer birthnull;while(rs.next()) {idrs.getInt(id);namers.getString(name);emailrs.getString(email);birthrs.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 stubString sqlselect * from costomers;;Connection conn null;try {//预编译sql语句Statement aconn.createStatement();//获取结果集的元数据ResultSet rsa.executeQuery(sql);System.out.println(------------------------);System.out.println(数据库显示内容如下:);System.out.println(------------------------);System.out.println(id 姓名 邮箱 生日);System.out.println( );Integer id null;String namenull;String emailnull;Integer birthnull;while(rs.next()) {idrs.getInt(id);namers.getString(name);emailrs.getString(email);birthrs.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 blocke.printStackTrace();}Scanner innew Scanner(System.in);System.out.println(请输入你的姓名:);String namein.nextLine();System.out.println(请输入你的登陆密码:);String p1in.next();System.out.println(请再次输入你的登陆密码:);String p2in.next();if(p1.equals(p2)) {String passwordp1;String sqlinsert 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 blocke.printStackTrace();}}private static void login() {try {Connection conn null;try {conn getConnection();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}Scanner innew Scanner(System.in);System.out.println(请输入你的姓名:);String namein.nextLine();System.out.println(请输入你的密码:);String passwordin.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 blocke.printStackTrace();}}Overridepublic 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 blocke.printStackTrace();}try {if(conn ! null)conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}//关闭资源的操作public static void closeResource(Connection conn,Statement ps,ResultSet rs) {try {if(ps ! null)ps.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}try {if(conn ! null)conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}try {if(rs ! null)rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blocke.printStackTrace();} finally {closeResource(con, pr);}return 0;}public T T find1(ClassT clazz,String sql,Object ...args) {Connection con null;PreparedStatement prnull;ResultSet renull;try {//获取连接con getConnection(); //预编译处理sql语句pr con.prepareStatement(sql);//填充占位符for(int i1;iargs.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 i0;icount;i) {//获取每一列的值Object value re.getObject(i1);//获取每个列的列名String columnName rsmd.getColumnName(i1);//给t对象指定的某个属性值依次赋值为value//得到属性Field field clazz.getDeclaredField(columnName);//保证可以访问防止是私有属性不可以访问field.setAccessible(true);field.set(t, value);}return t;}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {closeResource(con, pr, re);}return null;}public T ListT find2(ClassT clazz,String sql,Object ...args){Connection con null;PreparedStatement prnull;ResultSet renull;try {//获取连接con getConnection(); //预编译处理sql语句pr con.prepareStatement(sql);//填充占位符for(int i1;iargs.length;i) {pr.setObject(i, args[i-1]);}//接受结果re pr.executeQuery();//获取结果集的元数据ResultSetMetaData rsmd re.getMetaData();//通过元数据获取结果集的列数int count rsmd.getColumnCount();//创建集合ArrayListT list new ArrayListT();while(re.next()) {//if改为whileT t clazz.newInstance();//空的构造器for(int i0;icount;i) {//获取每一列的值Object value re.getObject(i1);//获取每个列的列名String columnName rsmd.getColumnName(i1);//给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 blocke.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 connectionurljdbc:mysql://localhost:3306/web01?serverTimezoneGMT;private static final String usernameroot;private static final String password0721;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 blocke.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 blocke.printStackTrace();}try {if(stmt!null)stmt.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}try {if(con!null)con.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 urljdbc:mysql://localhost:3306/web01?serverTimezoneGMT;String userroot;String password0721;Connection conDriverManager.getConnection(url, user, password); Statement stmtcon.createStatement();String sqlselect *from guanliyuan where id id and mimamima;ResultSet rs stmt.executeQuery(sql);if(rs.next()) {System.out.println(登陆成功);}else {System.out.println(未查询到该用户请选择是否注册1.是 2.否);Scanner wnew Scanner(System.in);int choicew.nextInt();switch(choice) {case 1:System.out.println(请输入添加的管理员账号、密码);String sidw.next();String smimaw.next();add2(sid,smima);break;case 2:System.out.println(欢迎下次使用); System.exit(0);} }} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static void add2(String id,String mima) {Connection cosnull;PreparedStatement stmnull;ResultSet rcnull;try {Class.forName(com.mysql.cj.jdbc.Driver);String urljdbc:mysql://localhost:3306/web01?serverTimezoneGMT;String userroot;String password0721;cosDriverManager.getConnection(url, user, password); String sqlinsert into guanliyuan(id,mima) value(?,?); stmcos.prepareStatement(sql);stm.setString(1, id);stm.setString(2, mima);int resultstm.executeUpdate();if(result0) {System.out.println(注册成功);menu();}else {System.out.println(注册失败);System.exit(0);}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {if(cos!null)try {cos.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}if(stm!null)try {stm.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}if(rc!null)try {rc.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}public static void menu() {System.out.println( 欢迎使用用户信息管理系统\n);Scanner s new Scanner(System.in);System.out.println(请先登录或注册1.登录 2.注册);int chos.nextInt();switch(cho) {case 1:System.out.println(请输入管理员账号);String ids.next();System.out.println(请输入密码);String mimas.next();denglu(id,mima);break;case 2:System.out.println(请输入添加的管理员账号、密码);String sids.next();String smimas.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 passs.next();add(user,pass);break;case 2:System.out.println(请输入要删除的用户ID);int idsss.nextInt();delete(idss);break;case 3:System.out.println(请输入查询的用户姓名、密码);String cnames.next();String cpasswords.next();find(cname,cpassword);break;case 4:System.out.println(请输入要修改密码用户的ID和新密码);int idss.nextInt();String newpasss.next();change(ids,newpass);break;case 5:all();break;case 6:paixu();break;case 7:System.out.println(请输入要查询第几页以及每页数据条数);int numbers.nextInt();int counts.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 connull;ResultSet rsnull;Statement stmtnull;try {conJDBC.getConnection();stmtcon.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 blocke.printStackTrace();} finally {JDBC.close(rs, stmt, con);}}public static boolean find(String username,String password) {Connection connull;Statement stmtnull;ResultSet rsnull;try {conJDBC.getConnection();stmtcon.createStatement();String sqlselect *from user where username username and passwordpassword;rsstmt.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 connull;ResultSet rsnull;PreparedStatement stmtnull;try {Class.forName(com.mysql.cj.jdbc.Driver);String urljdbc:mysql://localhost:3306/web01?serverTimezoneGMT;String userroot;String password0721;conDriverManager.getConnection(url, user, password);stmtcon.prepareStatement(select * from user limit ?,?);stmt.setInt(1,(pagenumber-1)*pagecount); stmt.setInt(2, pagecount);rsstmt.executeQuery();while(rs.next()) {System.out.println(rs.getInt(ID) rs.getString(username) rs.getString(password));}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {JDBC.close(rs, stmt, con);}}
//添加public static void add(String username,String password) {Connection connull;ResultSet rsnull;PreparedStatement stmtnull;try {conJDBC.getConnection();String sqlinsert into user(username,password) value(?,?);stmtcon.prepareStatement(sql);stmt.setString(1, username);stmt.setString(2, password);int resultstmt.executeUpdate();if(result0) {System.out.println(添加成功);}else {System.out.println(添加失败);}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} finally { JDBC.close(rs, stmt, con);}}//删除 public static void delete(int id) {Connection connull;ResultSet rsnull;PreparedStatement stmtnull;try {conJDBC.getConnection();String sqldelete from user where id?;stmtcon.prepareStatement(sql);stmt.setInt(1, id);int resultstmt.executeUpdate();if(result0) {System.out.println(删除成功);}else {System.out.println(删除失败);}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} finally { JDBC.close(rs, stmt, con);}}
//修改public static void change(int id,String newpassword) {Connection connull;ResultSet rsnull;PreparedStatement stmtnull;try {conJDBC.getConnection();String sqlupdate user set password? where id?;stmtcon.prepareStatement(sql);stmt.setString(1, newpassword);stmt.setInt(2, id);int resultstmt.executeUpdate();if(result0) {System.out.println(修改成功);}else {System.out.println(修改失败);}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} finally { JDBC.close(rs, stmt, con);}}public static void paixu() {Connection connull;ResultSet rsnull;Statement stmtnull;try {conJDBC.getConnection();stmtcon.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 blocke.printStackTrace();} finally {JDBC.close(rs, stmt, con);}}}
