主要功能是：利用JDBC来完成增删改查（多表连接查询）
我是把之前用IO流写把数据写入本地的学生管理系统，这次把存储方式换了改为把数据写入数据库里面。
我写jdbc的思路分为四步：
第一步： 写SQL语句
第二步： 从数据库连接中获取句柄
第三步： 传参数（设置参数）
第四步： 执行SQL语句
对了。还有一步是要创建几个类要跟数据库表一样（存放数据）
连接数据库
/*
* 1.导入jar包
* 2.加载驱动
* 3.连接数据库
* */
private static String name = "root";
private static String password = "she";
private static String url = "jdbc:mysql://localhost:3306/dd?useSSL=false";
private static Connection connection;
public UtilStudent() {
// TODO Auto-generated constructor stub
}
//加载驱动
static{
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("加载失败！！！");
}
}
//连接数据库
public static Connection getUtil() {
try {
connection = DriverManager.getConnection(url, name, password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("连接失败！！！");
}
return connection;
}
//释放connection
public static void closeConnection() {
if(connection!=null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}//账号
public class AccountNumber {
private String name;
private String password;
public AccountNumber() {
// TODO Auto-generated constructor stub
}
public AccountNumber( String name, String password) {
super();
this.name = name;
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "AccountNumber [name=" + name + ", password=" + password + "]";
}
}//学生
public class St {
private Integer id;
private String name;
public St() {
// TODO Auto-generated constructor stub
}
public St(Integer id, String name) {
super();
this.id = id;
this.name = name;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "St [id=" + id + ", name=" + name + "]";
}
}//老师
public class Lao {
private Integer s_id;
private String name;
public Lao() {
// TODO Auto-generated constructor stub
}
public Lao(Integer s_id, String name) {
super();
this.s_id = s_id;
this.name = name;
}
public Integer getS_id() {
return s_id;
}
public void setS_id(Integer s_id) {
this.s_id = s_id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Lao [s_id=" + s_id + ", name=" + name + "]";
}
}//成绩
public class Chen {
private Integer id;
private Integer s_id;
private String subject;
private Double chen;
public Chen() {
// TODO Auto-generated constructor stub
}
public Chen(Integer id, Integer s_id, String subject, Double chen) {
super();
this.id = id;
this.s_id = s_id;
this.subject = subject;
this.chen = chen;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getS_id() {
return s_id;
}
public void setS_id(Integer s_id) {
this.s_id = s_id;
}
public String getSubject() {
return subject;
}
public void setSubject(String subject) {
this.subject = subject;
}
public Double getChen() {
return chen;
}
public void setChen(Double chen) {
this.chen = chen;
}
@Override
public String toString() {
return "Chen [id=" + id + ", s_id=" + s_id + ", subject=" + subject + ", chen=" + chen + "]";
}
}//多表连接查询存放的数据
//我是根据表是需要显示那些数据来进行选择的
public class SelectAll {
private Integer laoId;
private String laoName;
private Integer stLao;
private String stName;
private String chenSubject;
private Double chenChen;
public SelectAll() {
// TODO Auto-generated constructor stub
}
public SelectAll(Integer laoId, String laoName, Integer stLao, String stName, String chenSubject, Double chenChen) {
super();
this.laoId = laoId;
this.laoName = laoName;
this.stLao = stLao;
this.stName = stName;
this.chenSubject = chenSubject;
this.chenChen = chenChen;
}
//
public Integer getLaoId() {
return laoId;
}
public void setLaoId(Integer laoId) {
this.laoId = laoId;
}
public String getLaoName() {
return laoName;
}
public void setLaoName(String laoName) {
this.laoName = laoName;
}
public Integer getStLao() {
return stLao;
}
public void setStLao(Integer stLao) {
this.stLao = stLao;
}
public String getStName() {
return stName;
}
public void setStName(String stName) {
this.stName = stName;
}
public String getChenSubject() {
return chenSubject;
}
public void setChenSubject(String chenSubject) {
this.chenSubject = chenSubject;
}
public Double getChenChen() {
return chenChen;
}
public void setChenChen(Double chenChen) {
this.chenChen = chenChen;
}
@Override
public String toString() {
return "SelectAll [老师编号="+laoId+",老师=" + laoName + ", 学号=" + stLao + ", 学生姓名=" + stName + ", 科目="
+ chenSubject + ", 成绩=" + chenChen + "]";
}
}public class Sqlstudent {
private Connection connection;
public Sqlstudent() {
// TODO Auto-generated constructor stub
this.connection=UtilStudent.getUtil();
}
/*
* 添加学生信息
* 添加账号
* 添加老师
* 添加成绩（添加成绩时要根据学生学号和老师编号来添加。要不然会报外键错误）
* */
public int addStudent(St st) {
String sql = "insert st values(?,?)";
PreparedStatement ps = null;
int len = 0;
try {
ps = connection.prepareStatement(sql);
ps.setInt(1, st.getId());
ps.setString(2, st.getName());
len = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(ps!=null) {
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return len;
}
public int addAccountNumber(AccountNumber an) {
//1.写sql语句
String sql = "insert account_number(`name`,`password`) values(?,?)";
PreparedStatement ps = null;
int len=0;
try {
//2.连接句柄
ps = connection.prepareStatement(sql);
//3.传参数
ps.setString(1, an.getName());//获取AccountNumber类里面的数据
ps.setString(2, an.getPassword());
//4.执行
len = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(ps!=null) {
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return len;
}
public int addLao(Lao lao) {
String sql = "insert lao values(?,?)";
PreparedStatement ps = null;
int len = 0;
try {
ps = connection.prepareStatement(sql);
ps.setInt(1, lao.getS_id());
ps.setString(2, lao.getName());
len = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(ps!=null) {
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return len;
}
public int addChen(Chen chen) {
String sql = "insert chen values(?,?,?,?)";
PreparedStatement ps = null;
int len = 0;
try {
ps = connection.prepareStatement(sql);
ps.setInt(1, chen.getId());
ps.setInt(2, chen.getS_id());
ps.setString(3, chen.getSubject());
ps.setDouble(4, chen.getChen());
len = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
// e.printStackTrace();
System.out.println("插入失败！！");
System.out.println("请核对“学生学号”和“老师编号”来插入数据");
}finally {
try {
if(ps!=null) {
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return len;
}
/*
* 修改学生信息
* 修改老师信息
* 修改成绩（修改成绩时要根据学生学号和老师编号来修改。要不然会报外键错误）
* */
public int setStudent(St st) {
String sql = "update st set `name`=? where id=?";
PreparedStatement ps = null;
int len = 0;
try {
ps = connection.prepareStatement(sql);
ps.setString(1, st.getName());
ps.setInt(2, st.getId());
len = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(ps!=null) {
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return len;
}
public int setLao(Lao lao) {
String sql = "update lao set `name`=? where s_id=?";
PreparedStatement ps = null;
int len = 0;
try {
ps = connection.prepareStatement(sql);
ps.setString(1, lao.getName());
ps.setInt(2, lao.getS_id());
len = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(ps!=null) {
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return len;
}
public int setChen(Chen chen) {
String sql = "update chen set s_id=?,`subject`=?,chen=? where id=?";
PreparedStatement ps = null;
int len = 0;
try {
ps = connection.prepareStatement(sql);
ps.setInt(1, chen.getS_id());
ps.setString(2, chen.getSubject());
ps.setDouble(3, chen.getChen());
ps.setInt(4, chen.getId());
len = ps.executeUpdate();
if(len != 0) {
System.out.println("修改成功！！");
}
if(len == 0) {
System.out.println("修改失败！！");
System.out.println("请核对“学生学号”和“老师编号”来修改数据");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("修改失败！！");
System.out.println("请核对“学生学号”和“老师编号”来修改数据");
}finally {
try {
if(ps!=null) {
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return len;
}
/*
* 先删除成绩，在删学生和老师（如果不先删除成绩。学生表和老师表会发生外键报错）
* 因为学生表、老师表、成绩表、他们是多对多关系
* */
public int deleteLao(int id) {
String sql = "delete from lao where s_id=?";
PreparedStatement ps = null;
PreparedStatement ps1 = null;
int len = 0;
try {
ps = connection.prepareStatement(sql);
ps.setInt(1, id);
ps.executeUpdate();
len = ps1.executeUpdate();
if(len!=0) {
System.out.println("删除成功！！");
}
if(len==0) {
System.out.println("删除失败！！");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(ps!=null) {
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return len;
}
public int deleteChen(int id) {
String sql = "delete from chen where id=?";
String sql1 = "delete from st where id=?";
PreparedStatement ps = null;
PreparedStatement ps1 = null;
int len = 0;
try {
ps = connection.prepareStatement(sql);
ps1 = connection.prepareStatement(sql1);
ps.setInt(1, id);
ps1.setInt(1, id);
ps.executeUpdate();
len = ps1.executeUpdate();
if(len!=0) {
System.out.println("删除成功！！");
}
if(len==0) {
System.out.println("删除失败！！");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(ps!=null) {
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return len;
}
/*
* 多表连查和普通的查询
* */
public AccountNumber selectAccountNumber(String name,String password) {
//1.写sql语句
String sql = "select * from account_number where `name`=? and `password`=?";
PreparedStatement ps = null;
ResultSet rs = null;
AccountNumber an = null;
try {
//连接句柄
ps = connection.prepareStatement(sql);
//传参数
ps.setString(1, name);
ps.setString(2, password);
//执行
rs = ps.executeQuery();
while(rs.next()) {
an = new AccountNumber();
an.setName(rs.getString("name"));
an.setPassword(rs.getString("password"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(ps!=null) {
ps.close();
}
if(rs!=null) {
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return an;
}
public List<SelectAll> selectAll(int id,String name){
String sql = "select lao.s_id,lao.`name`,st.id,st.`name`,chen.`subject`,chen.chen from lao \r\n" +
"left join chen on lao.s_id=chen.s_id\r\n" +
"inner join st on chen.id=st.id where st.id=? and st.`name`=?";
PreparedStatement ps = null;
ResultSet rs = null;
List<SelectAll> sa = null;
try {
ps = connection.prepareStatement(sql);
ps.setInt(1, id);
ps.setString(2, name);
rs = ps.executeQuery();
while(rs.next()) {
if(sa == null) {
sa = new ArrayList<SelectAll>();
}
SelectAll selectAll = new SelectAll();
selectAll.setLaoId(rs.getInt("lao.s_id"));
selectAll.setLaoName(rs.getString("lao.name"));;
selectAll.setStLao(rs.getInt("st.id"));
selectAll.setStName(rs.getString("st.name"));
selectAll.setChenSubject(rs.getString("chen.subject"));
selectAll.setChenChen(rs.getDouble("chen.chen"));
sa.add(selectAll);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return sa;
}
public List<SelectAll> selectAlll(){
String sql = "select lao.s_id,lao.`name`,st.id,st.`name`,chen.`subject`,chen.chen from lao \r\n" +
"left join chen on lao.s_id=chen.s_id\r\n" +
"inner join st on chen.id=st.id";
PreparedStatement ps = null;
ResultSet rs = null;
List<SelectAll> sa = null;
try {
ps = connection.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()) {
if(sa == null) {
sa = new ArrayList<SelectAll>();
}
SelectAll selectAll = new SelectAll();
selectAll.setLaoId(rs.getInt("lao.s_id"));
selectAll.setLaoName(rs.getString("lao.name"));;
selectAll.setStLao(rs.getInt("st.id"));
selectAll.setStName(rs.getString("st.name"));
selectAll.setChenSubject(rs.getString("chen.subject"));
selectAll.setChenChen(rs.getDouble("chen.chen"));
sa.add(selectAll);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return sa;
}
public List<AccountNumber> selectAccountNumber1() {
String sql = "select * from account_number";
PreparedStatement ps = null;
ResultSet rs = null;
List<AccountNumber> an = null;
try {
ps = connection.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()) {
if(an == null) {
an = new ArrayList<AccountNumber>();
}
AccountNumber accountNumber = new AccountNumber();
accountNumber.setName(rs.getString("name"));
accountNumber.setPassword(rs.getString("password"));
an.add(accountNumber);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return an;
}
}public class Student_mark {
Scanner scanner = new Scanner(System.in);
private Sqlstudent sql = new Sqlstudent();
private AccountNumber accountN;
private Chen chen;
private Lao lao;
private St st;
private int id;
public Student_mark() {
// TODO Auto-generated constructor stub
}
public void register() {
accountN = new AccountNumber();
System.out.print("请输入账号：");
String name = scanner.nextLine();
System.out.print("请输入密码：");
String password = scanner.nextLine();
accountN = sql.selectAccountNumber(name, password);//把取出来的数据放进AccountNumber表保存
if(this.accountN.getName().equals(name)&&this.accountN.getPassword().equals(password)) {
//在把AccountNumber表的数据跟用户输入的进行比较
studentSystem();
return;
}
System.out.println("账号或者密码输入错误！！！请重新输入");
register();
}
public String catalogue() {
System.out.println("插入学生信息：（1）");
System.out.println("插入老师信息：（2）");
System.out.println("插入学生成绩：（3）");
System.out.println("显示学生和老师信息：（4）");
System.out.println("根据ID或者学生姓名查询学生成绩：（5）");
System.out.println("修改学生信息：（6）");
System.out.println("修改老师信息：（7）");
System.out.println("修改成绩信息：（8）");
System.out.println("根据ID删除学生信息：（9）");
System.out.println("根据老师ID删除老师信息：（10）");
System.out.println("注册：（11）");
System.out.println("显示账号密码：（12）");
System.out.println("退出！！！（13）");
return scanner.nextLine();
}
public void studentSystem() {
String saveNumber = catalogue();
switch (saveNumber) {
case "1":
addStudent();
studentSystem();
break;
case "2":
addLao();
studentSystem();
break;
case "3":
addChen();
studentSystem();
break;
case "4":
show();
studentSystem();
break;
case "5":
showStudent();
studentSystem();
break;
case "6":
alterStudent();
studentSystem();
break;
case "7":
alterLao();
studentSystem();
break;
case "8":
alterChen();
studentSystem();
break;
case "9":
deleteStudent();
studentSystem();
break;
case "10":
deleteLao();
studentSystem();
break;
case "11":
addName();
studentSystem();
break;
case "12":
showName();
studentSystem();
break;
case "13":
UtilStudent.closeConnection();
break;
}
}
public void show() {
List<SelectAll> select = sql.selectAlll();
for (SelectAll selectAll : select) {
System.out.println(selectAll);
}
}
public void addStudent() {
st = new St();
System.out.println("请输入学生学号：");
st.setId(Integer.parseInt(scanner.nextLine()));
System.out.println("请输入学生姓名：");
st.setName(scanner.nextLine());
System.out.print("你是否要添加（y/n）");
String k = scanner.nextLine();
// showStudent();
if("y".equals(k) || "Y".equals(k)) {
sql.addStudent(st);
System.out.println("添加成功！！！");
studentSystem();
}else {
System.out.println("取消！");
}
}
public void addLao() {
lao = new Lao();
System.out.println("请输入老师编号：");
lao.setS_id(Integer.parseInt(scanner.nextLine()));
System.out.println("请输入老师姓名：");
lao.setName(scanner.nextLine());
System.out.print("你是否要添加（y/n）");
String k = scanner.nextLine();
// showStudent();
if("y".equals(k) || "Y".equals(k)) {
sql.addLao(lao);
System.out.println("添加成功！！！");
studentSystem();
}else {
System.out.println("取消！");
}
}
public void addChen() {
chen = new Chen();
System.out.println("请输入学生学号：");
chen.setId(Integer.parseInt(scanner.nextLine()));
System.out.println("请输入老师编号：");
chen.setS_id(Integer.parseInt(scanner.nextLine()));
System.out.println("请输入科目：");
chen.setSubject(scanner.nextLine());
System.out.println("请输入成绩：");
chen.setChen(Double.parseDouble(scanner.nextLine()));
System.out.print("你是否要添加（y/n）");
String k = scanner.nextLine();
// showStudent();
if("y".equals(k) || "Y".equals(k)) {
sql.addChen(chen);
studentSystem();
}else {
System.out.println("取消！");
}
}
public void showStudent() {
System.out.println("请输入学生学号：");
int id = Integer.parseInt(scanner.nextLine());
System.out.println("请输入学生姓名：");
String name = scanner.nextLine();
List<SelectAll> select = sql.selectAll(id, name);
for (SelectAll selectAll : select) {
System.out.println(selectAll);
}
}
//根据ID删除学生信息
public void deleteStudent() {
System.out.print("请输入需要删除的学生ID：");
int alterID = Integer.parseInt(scanner.nextLine());
System.out.print("是否要删除（y/n）：");
String k = scanner.nextLine();
if("y".equals(k) || "Y".equals(k)) {
sql.deleteChen(alterID);
}else {
System.out.println("取消！");
studentSystem();
}
}
public void deleteLao() {
System.out.print("请输入需要删除的老师编号：");
int alterID = Integer.parseInt(scanner.nextLine());
System.out.print("是否要删除（y/n）：");
String k = scanner.nextLine();
if("y".equals(k) || "Y".equals(k)) {
sql.deleteLao(alterID);
}else {
System.out.println("取消！");
studentSystem();
}
}
//根据ID修改学生信息
public void alterStudent() {
st = new St();
System.out.println("请输入你要修改的内容：");
System.out.print("请输入学生学号：");
st.setId(Integer.parseInt(scanner.nextLine()));
System.out.print("请输入学生姓名：");
st.setName(scanner.nextLine());
System.out.print("是否要修改（y/n）：");
String k = scanner.nextLine();
if("y".equals(k) || "Y".equals(k)) {
sql.setStudent(st);
System.out.println("修改成功！！");
}else {
System.out.println("取消！");
studentSystem();
}
}
public void alterLao() {
lao = new Lao();
System.out.println("请输入你要修改的内容：");
System.out.print("请输入老师编号：");
lao.setS_id(Integer.parseInt(scanner.nextLine()));
System.out.print("请输入老师姓名：");
lao.setName(scanner.nextLine());
System.out.print("是否要修改（y/n）：");
String k = scanner.nextLine();
if("y".equals(k) || "Y".equals(k)) {
sql.setLao(lao);
System.out.println("修改成功！！");
}else {
System.out.println("取消！");
studentSystem();
}
}
public void alterChen() {
chen = new Chen();
System.out.println("请输入你要修改的内容：");
System.out.print("请输入学生学号：");
chen.setId(Integer.parseInt(scanner.nextLine()));
System.out.print("请输入老师编号：");
chen.setS_id(Integer.parseInt(scanner.nextLine()));
System.out.print("请输入科目：");
chen.setSubject(scanner.nextLine());
System.out.print("请输入成绩：");
chen.setChen(Double.parseDouble(scanner.nextLine()));
System.out.print("是否要修改（y/n）：");
String k = scanner.nextLine();
if("y".equals(k) || "Y".equals(k)) {
sql.setChen(chen);
studentSystem();
}else {
System.out.println("取消！");
studentSystem();
}
}
public void addName() {
accountN = new AccountNumber();
System.out.print("你要添加的账户：");
accountN.setName(scanner.nextLine());
System.out.print("你要添加的密码：");
accountN.setPassword(scanner.nextLine());
sql.addAccountNumber(accountN);
}
public void showName() {
List<AccountNumber> select = sql.selectAccountNumber1();
for (AccountNumber accountNumber : select) {
System.out.println(accountNumber);
}
}
}测试类
public class Text {
public static void main(String[] args) {
// TODO Auto-generated method stub
Student_mark studentMark = new Student_mark();
studentMark.register();
}
}数据库表：
运行结果
我只选多表连接查询的截图
总结：
主要难在我多表连接查询做的时候没有细心观察。我创建了一个表用于保存表查出来的数据的类。在把数据保存给集合。我这边之前把SQL语句写的有问题。整了我40分钟。才找出原因。↓
我在复制过来前把\r\n都删除了。你没有/r(空格)语句就会直接连接在一起。导致语句错误无法执行。
Unknown column ‘chen.s_idinner’ in ‘on clause’ 翻译的意思是：未知列’chen。s_idinner’ in 'on子句
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
