文章目录
1 使用JDBC实现学生信息管理系统
1.1 建库、建表SQL语句如下：
-- 1、创建数据库jt_db数据库(如果不存在才创建)
create database if not exists jt_db charset utf8;
use jt_db; -- 选择jt_db数据库
-- 2、在 jt_db 库中创建 stu 表(学生表)
drop table if exists stu;
create table stu(
stuid varchar(50),
name varchar(50),
gender char(2),
addr varchar(50),
score double
);
-- 3、往 stu 表中, 插入记录
insert into stu values('1001','张三','男', '北京', 86);
1.2 功能实现
1.2.0 模板
运行程序控制台提示如下：
输入a：查询所有学生信息
输入b：添加学生信息
输入c：根据id修改学生信息
输入d：根据id删除学生信息
代码模板：
package com.edut.cn.tarena.jdbc.stumamager.my;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Scanner;
public class StudentManager {
private static Scanner sc = new Scanner(System.in);
public static void main(String[] args) {
new StudentManager().launch();
}
private void launch() {
showTips();
boolean flag =true; //false 结束
while(flag) {
String s = sc.nextLine();
flag = doSelect(s);
}
shotDown();
}
private boolean doSelect(String s) {
switch (s) {
case "a":
retrieveStuInfo();
break;
case "b" :
createStuInfo();
break;
case "c":
updateStuInfo();
break;
case "d":
deleteStuInfo();
break;
case "h":
showTips();
break;
case "#exit":
return false;
default:
System.out.println("输入错误,请重新输入...");
break;
}
return true;
}
private void deleteStuInfo() {
// TODO Auto-generated method stub
}
private void updateStuInfo() {
// TODO Auto-generated method stub
}
private void createStuInfo() {
// TODO Auto-generated method stub
}
private void retrieveStuInfo() {
// TODO Auto-generated method stub
}
private void shotDown() {
// TODO Auto-generated method stub
System.out.println("退出系统...");
}
private void showTips() {
System.out.print("a：查询学生信息\t");
System.out.print("b：添加学生信息\t");
System.out.print("c：修改学生信息\t");
System.out.print("d：删除学生信息\t");
System.out.print("\r\nh：帮助\t");
System.out.print("#exit：退出\t");
System.out.println();
System.out.println("请输入操作，abcd任选一项：");
}
}
模板测试：
1.2.1 查询所有学生信息
查找功能
private void retrieveStuInfo() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtil.getConn();
String sql = "select * from stu";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
ResultSetMetaData md = rs.getMetaData();
int col = md.getColumnCount() ;
// 打印表头
StringBuilder sb = new StringBuilder();
for (int i = 1; i <= col; i++) {
sb.append(md.getColumnName(i)) ;
sb.append(" - ");
}
sb.delete(sb.length()-3, sb.length()); ;
System.out.println(sb);
//打印数据
sb.delete(0, sb.length()) ;
while(rs.next()) {
for (int i = 1; i <= col; i++) {
Object obj = rs.getObject(i);
sb.append(obj);
sb.append(" - ");
}
sb.delete(sb.length()-3, sb.length());
System.out.println(sb);
sb.delete(0, sb.length());
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtil.close(conn,ps,rs);
}
}
查找功能测试：
1.2.2 添加学生信息
添加功能代码：
private void createStuInfo() {
System.out.println("录入数据：");
String id = scanID("\\d{4}");
String name = scanString("名字" , ".*");
String gender = scanString("性别",".{0,2}");
String addr = scanString("地址",".*");
double score =scanDouble("分数"); ;
Connection conn = null ;
PreparedStatement ps = null;
try {
conn = JdbcUtil.getConn();
//id ， 名字String， 性别char， 地址String， 分数double
String sql = "insert into stu value(? ,?,?,?,?);" ;
ps = conn.prepareStatement(sql);
ps.setString(1, id);
ps.setString(2, name);
ps.setString(3, gender);
ps.setString(4, addr);
ps.setDouble(5, score);
int col = ps.executeUpdate();
//查看
System.out.println("添加数据："+name+" - "+gender+" - "+addr + " - "+ score);
System.out.println("影响行数："+col);
System.out.println("添加结果：");
this.retrieveStuInfo();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtil.close(conn, ps, null);
}
}
private String scanString(String name, String exp) {
while(true) {
System.out.print(name+"=");
String s = sc.nextLine();
if(s.matches(exp)) {
return s ;
}else {
System.out.println("请输入正确格式..."+exp);
}
}
}
private String scanID(String exp) {
return scanString("ID",exp);
}
private double scanDouble(String name) {
double score = Double.parseDouble(scanString(name,"\\d+[.]?\\d*")) ;
return score;
}
添加功能测试：
1.2.3 根据id修改学生信息
修改功能代码：
private void updateStuInfo() {
Connection conn = null ;
PreparedStatement ps = null;
System.out.println("请输入修改的 ID 号：");
String ID = scanID("\\d{4}") ;
System.out.println("ID="+ID+" 数据修改：");
String name = scanString("名字" , ".*");
String gender = scanString("性别",".{0,2}");
String addr = scanString("地址",".*");
double score =scanDouble("分数"); ;
try {
conn = JdbcUtil.getConn();
StringBuilder sql = new StringBuilder("update stu set ");
sql.append(" name=? , ") ;
sql.append(" gender=? , ") ;
sql.append(" addr=? , ") ;
sql.append(" score=? , ") ;
sql.delete(sql.length()-2, sql.length());
sql.append(" where stuid=?");
ps = conn.prepareStatement(sql.toString()) ;
ps.setString(1, name);
ps.setString(2, gender);
ps.setString(3, addr);
ps.setDouble(4, score);
ps.setString(5, ID);
int col = ps.executeUpdate();
this.retrieveStuInfo();
System.out.println("影响行数:"+col);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtil.close(conn, ps, null);
}
}
修改功能测试：
1.2.4 根据id删除学生信息
删除功能代码
private void deleteStuInfo() {
System.out.println("输入要删除的ID...");
String ID = scanID() ;
Connection conn = null;
PreparedStatement ps = null ;
try {
conn = JdbcUtil.getConn() ;
String sql = "DELETE FROM stu where stuid=? ;" ;
ps = conn.prepareStatement(sql);
ps.setString(1, ID);
int col = ps.executeUpdate();
retrieveStuInfo();
System.out.println("影响行数："+col);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtil.close(conn, ps, null);
}
}
删除功能测试
简单封装的工具类 - JdbcUtil
package com.edut.cn.tarena.jdbc.stumamager.my;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcUtil {
/** * 获得连接器 */
public static Connection getConn() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/jt_db?characterEncoding=gbk",
"root",
"root"
);
return conn;
}
/** * 关闭传入的连接 */
public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
conn = null;
}
}
if(ps!=null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
ps= null;
}
}
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
rs = null;
}
}
}
}
完整代码
工具类 - JdbcUtil
package com.edut.cn.tarena.jdbc.stumamager.my;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcUtil {
/** * 获得连接器 */
public static Connection getConn() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/jt_db?characterEncoding=gbk",
"root",
"root"
);
return conn;
}
/** * 关闭传入的连接 */
public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
conn = null;
}
}
if(ps!=null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
ps= null;
}
}
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
rs = null;
}
}
}
}
功能实现 - 代码 - StudentManager
package com.edut.cn.tarena.jdbc.stumamager.my;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Scanner;
import javax.rmi.ssl.SslRMIClientSocketFactory;
public class StudentManager {
private static Scanner sc = new Scanner(System.in);
public static void main(String[] args) {
new StudentManager().launch();
}
private void launch() {
showTips();
boolean flag =true; //false 结束
while(flag) {
String s = sc.nextLine();
flag = doSelect(s);
}
shotDown();
}
private boolean doSelect(String s) {
System.out.println("---------------------");
switch (s) {
case "a":
retrieveStuInfo(); //查找
break;
case "b" :
createStuInfo(); //添加
break;
case "c":
updateStuInfo(); //修改
break;
case "d":
deleteStuInfo(); //删除
break;
case "h":
showTips(); //提示
break;
case "#exit": //退出
return false;
default:
System.out.println("输入错误,请重新输入..."); //其他
break;
}
return true;
}
private void deleteStuInfo() {
System.out.println("输入要删除的ID...");
String ID = scanID() ;
Connection conn = null;
PreparedStatement ps = null ;
try {
conn = JdbcUtil.getConn() ;
String sql = "DELETE FROM stu where stuid=? ;" ;
ps = conn.prepareStatement(sql);
ps.setString(1, ID);
int col = ps.executeUpdate();
retrieveStuInfo();
System.out.println("影响行数："+col);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtil.close(conn, ps, null);
}
}
private void createStuInfo() {
System.out.println("录入数据...");
String id = scanID();
String name = scanString("名字" , ".*");
String gender = scanString("性别",".{0,2}");
String addr = scanString("地址",".*");
double score =scanDouble("分数"); ;
Connection conn = null ;
PreparedStatement ps = null;
try {
conn = JdbcUtil.getConn();
//id ， 名字String， 性别char， 地址String， 分数double
String sql = "insert into stu value(? ,?,?,?,?);" ;
ps = conn.prepareStatement(sql);
ps.setString(1, id);
ps.setString(2, name);
ps.setString(3, gender);
ps.setString(4, addr);
ps.setDouble(5, score);
int col = ps.executeUpdate();
//查看
System.out.println("添加数据："+name+" - "+gender+" - "+addr + " - "+ score);
System.out.println("影响行数："+col);
System.out.println("添加结果：");
this.retrieveStuInfo();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtil.close(conn, ps, null);
}
}
private String scanString(String name, String exp) {
while(true) {
System.out.print(name+"=");
String s = sc.nextLine();
if(s.matches(exp)) {
return s ;
}else {
System.out.println("请输入正确格式..."+exp);
}
}
}
private String scanID(String exp) {
return scanString("ID",exp);
}
private String scanID() {
return scanID("\\d{4}");
}
private double scanDouble(String name) {
double score = Double.parseDouble(scanString(name,"\\d+[.]?\\d*")) ;
return score;
}
private void updateStuInfo() {
Connection conn = null ;
PreparedStatement ps = null;
System.out.println("请输入修改的 ID 号：");
String ID = scanID("\\d{4}") ;
System.out.println("ID="+ID+" 数据修改：");
String name = scanString("名字" , ".*");
String gender = scanString("性别",".{0,2}");
String addr = scanString("地址",".*");
double score =scanDouble("分数"); ;
try {
conn = JdbcUtil.getConn();
StringBuilder sql = new StringBuilder("update stu set ");
sql.append(" name=? , ") ;
sql.append(" gender=? , ") ;
sql.append(" addr=? , ") ;
sql.append(" score=? , ") ;
sql.delete(sql.length()-2, sql.length());
sql.append(" where stuid=?");
ps = conn.prepareStatement(sql.toString()) ;
ps.setString(1, name);
ps.setString(2, gender);
ps.setString(3, addr);
ps.setDouble(4, score);
ps.setString(5, ID);
int col = ps.executeUpdate();
this.retrieveStuInfo();
System.out.println("影响行数:"+col);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtil.close(conn, ps, null);
}
}
private void retrieveStuInfo() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtil.getConn();
String sql = "select * from stu";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
ResultSetMetaData md = rs.getMetaData();
int col = md.getColumnCount() ;
// 打印表头
StringBuilder sb = new StringBuilder();
for (int i = 1; i <= col; i++) {
sb.append(md.getColumnName(i)) ;
sb.append(" - ");
}
sb.delete(sb.length()-3, sb.length()); ;
System.out.println(sb);
//打印数据
sb.delete(0, sb.length()) ;
while(rs.next()) {
for (int i = 1; i <= col; i++) {
Object obj = rs.getObject(i);
sb.append(obj);
sb.append(" - ");
}
sb.delete(sb.length()-3, sb.length());
System.out.println(sb);
sb.delete(0, sb.length());
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtil.close(conn,ps,rs);
}
}
private void shotDown() {
// TODO Auto-generated method stub
System.out.println("退出系统...");
}
private void showTips() {
System.out.print("a：查询学生信息\t");
System.out.print("b：添加学生信息\t");
System.out.print("c：修改学生信息\t");
System.out.print("d：删除学生信息\t");
System.out.print("\r\nh：帮助\t");
System.out.print("#exit：退出\t");
System.out.println();
System.out.println("请输入操作，abcd任选一项：");
}
}
京公网安备 11010502036488号