老师（学生）信息管理系统（JDBC如何连接SQL）

学习SQL，高效管理数据库信息 #生活技巧# #工作学习技巧# #数字技能学习#

mysql-connector-java-5.1.46 架包下载

JDBC操作数据库的步骤

1. 加载驱动。

Class.forName("com.mysql.jdbc.Driver");

2.创建连接。

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test");

3. 创建语句。

PreparedStatement ps = con.prepareStatement("select * from emp where sal between ? and ?");

    ps.setInt(1, 1000);

    ps.setInt(2, 3000);

4. 执行语句。
   

ResultSet rs = ps.executeQuery();

5. 处理结果。

while(rs.next()) {

        System.out.println(rs.getInt("empno") + " - " + rs.getString("ename"));

    }

6. 关闭资源。
   

finally {

if(con != null) {

try {

                con.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

提示：关闭外部资源的顺序应该和打开的顺序相反，也就是说先关闭ResultSet、再关闭Statement、在关闭Connection。上面的代码只关闭了Connection（连接），虽然通常情况下在关闭连接时，连接上创建的语句和打开的游标也会关闭，但不能保证总是如此，因此应该按照刚才说的顺序分别关闭。此外，第一步加载驱动在JDBC 4.0中是可以省略的（自动从类路径中加载驱动），但是我们建议保留。

整体包类如图：

数据库t_teacher表如下

t_userinfo表

工具类 DBUtil

package com.baidu.stumanager.util;

import java.sql.Connection;

import java.sql.DriverManager;

public class DBUtil {

private Connection conn = null;

public Connection getConn() {

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/lanoudb?useUnicode=true&characterEncoding=UTF-8", "root", "123");

} catch (Exception e) {

e.printStackTrace();

}

return conn;

}

public void close(){

try{

if(!conn.isClosed()){

conn.close();

}

}catch(Exception e){

e.printStackTrace();

}

}

}

TeacherDao 接口

package com.baidu.stumanager.dao;

import java.util.List;

import com.baidu.stumanager.domain.Teacher;

public interface TeacherDao {

public int saveTeacher(Teacher teacher);

public int deleteTeacher(int tid);

public int updateTeacher(Teacher teacher);

public List<Teacher> getTeachers();

public Teacher getTeachcerById(int tid);

public List<Teacher> getTeacherByName(String name);

public List<Teacher> getTeacherByPage(int pageIndex);

}

UserinfoDao

package com.baidu.stumanager.dao;

import com.baidu.stumanager.domain.Userinfo;

public interface UserinfoDao {

public Userinfo getUserinfoByIdAndPwd(Userinfo user);

public int register(Userinfo userinfo);

}

TeacherImpl

package com.baidu.stumanager.dao.impl;

import java.security.interfaces.RSAKey;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.baidu.stumanager.dao.TeacherDao;

import com.baidu.stumanager.domain.Teacher;

import com.baidu.stumanager.util.DBUtil;

public class TeacherImpl extends DBUtil implements TeacherDao {

private List<Teacher> teachers = null;

private Connection conn = null;

private PreparedStatement pstm = null;

private ResultSet rs = null;

String sql = "";

public int saveTeacher(Teacher teacher) {

conn = getConn();

try {

String sql = "insert into t_teacher(tid,tname) values(?,?)";

pstm = conn.prepareStatement(sql);

pstm.setInt(1, teacher.getTid());

pstm.setString(2, teacher.getTname());

return pstm.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

} finally {

close();

}

return 0;

}

public int deleteTeacher(int tid) {

conn = getConn();

try {

String sql = "delete from t_teacher where tid=?";

pstm = conn.prepareStatement(sql);

pstm.setInt(1, tid);

return pstm.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

} finally {

close();

}

return 0;

}

public int updateTeacher(Teacher teacher) {

conn = getConn();

try {

String sql = "update t_teacher set tname=? where tid=?";

pstm = conn.prepareStatement(sql);

pstm.setInt(2, teacher.getTid());

pstm.setString(1, teacher.getTname());

return pstm.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

} finally {

close();

}

return 0;

}

public List<Teacher> getTeachers() {

teachers = new ArrayList<Teacher>();

conn = getConn();

try {

String sql = "select tid,tname from t_teacher";

pstm = conn.prepareStatement(sql);

ResultSet rs = pstm.executeQuery();

while (rs.next()) {

Teacher teacher = new Teacher();

teacher.setTid(rs.getInt("tid"));

teacher.setTname(rs.getString("tname"));

teachers.add(teacher);

}

} catch (Exception e) {

e.printStackTrace();

} finally {

close();

}

return teachers;

}

@Override

public Teacher getTeachcerById(int tid) {

Teacher teacher = null;

conn = getConn();

sql = "select tid, tname from t_teacher where tid = ?";

try {

pstm = conn.prepareStatement(sql);

pstm.setInt(1, tid);

rs = pstm.executeQuery();

while (rs.next()) {

teacher = new Teacher();

teacher.setTid(rs.getInt("tid"));

teacher.setTname(rs.getString("tname"));

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

close();

}

return teacher;

}

@Override

public List<Teacher> getTeacherByName(String tname) {

conn = getConn();

Teacher teacher = null;

teachers = new ArrayList<Teacher>();

try {

sql = "select tid, tname from t_teacher where tname like ?";

pstm = conn.prepareStatement(sql);

pstm.setString(1, "%"+ tname + "%");

rs = pstm.executeQuery();

while (rs.next()) {

teacher = new Teacher();

teacher.setTid(rs.getInt("tid"));

teacher.setTname(rs.getString("tname"));

teachers.add(teacher);

}

} catch (SQLException e) {

e.printStackTrace();

}

return teachers;

}

@Override

public List<Teacher> getTeacherByPage(int pageIndex) {

conn = getConn();

Teacher teacher = null;

teachers = new ArrayList<>();

sql = "select tid,tname from t_teacher limit ?,3";

try {

pstm = conn.prepareStatement(sql);

pstm.setInt(1, (pageIndex - 1) * 3);

rs = pstm.executeQuery();

while (rs.next()) {

teacher = new Teacher();

teacher.setTid(rs.getInt("tid"));

teacher.setTname(rs.getString("tname"));

System.out.println(teacher);

teachers.add(teacher);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

close();

}

return teachers;

}

}

UserinfoImpl

package com.baidu.stumanager.dao.impl;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.baidu.stumanager.dao.UserinfoDao;

import com.baidu.stumanager.domain.Userinfo;

import com.baidu.stumanager.util.DBUtil;

public class UserinfoImpl extends DBUtil implements UserinfoDao{

private Connection conn = null;

private PreparedStatement pstm = null;

private ResultSet rs = null;

private String sql = null;

private List<Userinfo> userinfos = null;

@Override

public Userinfo getUserinfoByIdAndPwd(Userinfo user) {

Userinfo userinfo = null;

userinfos = new ArrayList<>();

conn = getConn();

sql = "select userid, userpwd, userage from t_userinfo where userid = ? and userpwd = ?";

try {

pstm = conn.prepareStatement(sql);

pstm.setString(1, user.getUserid());

pstm.setString(2, user.getUserpwd());

rs = pstm.executeQuery();

while (rs.next()) {

userinfo = new Userinfo();

userinfo.setUserid(rs.getString("userid"));

userinfo.setUserpwd(rs.getString("userpwd"));

userinfo.setUserage(rs.getInt("userage"));

}

} catch (SQLException e) {

e.printStackTrace();

}

return userinfo;

}

@Override

public int register(Userinfo userinfo) {

conn = getConn();

sql = "select userid from t_userinfo";

try {

pstm = conn.prepareStatement(sql);

rs = pstm.executeQuery();

while (rs.next()) {

if (userinfo.getUserid().equals(rs.getString("userid"))) {

System.out.println("用户名已存在，请重新注册！！");

return 0;

}

}

sql = "insert into t_userinfo (userid, userpwd, userage) values (?, ?, ?)";

pstm =conn.prepareStatement(sql);

pstm.setString(1, userinfo.getUserid());

pstm.setString(2, userinfo.getUserpwd());

pstm.setInt(3, userinfo.getUserage());

pstm.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

} finally {

close();

}

return 1;

}

}

Teacher

package com.baidu.stumanager.domain;

public class Teacher {

private int tid;

private String tname;

public int getTid() {

return tid;

}

public void setTid(int i) {

this.tid = i;

}

public String getTname() {

return tname;

}

public void setTname(String tname) {

this.tname = tname;

}

@Override

public String toString() {

return "Teacher [tid=" + tid + ", tname=" + tname + "]";

}

}

Userinfo

package com.baidu.stumanager.domain;

public class Userinfo {

private String userid;

private String userpwd;

private int userage;

public String getUserid() {

return userid;

}

public void setUserid(String userid) {

this.userid = userid;

}

public String getUserpwd() {

return userpwd;

}

public void setUserpwd(String userpwd) {

this.userpwd = userpwd;

}

public int getUserage() {

return userage;

}

public void setUserage(int userage) {

this.userage = userage;

}

@Override

public String toString() {

return "Userinfo [userid=" + userid + ", userpwd=" + userpwd + ", userage=" + userage + "]";

}

}

Test1

package com.baidu.stumanager.test;

import java.util.Scanner;

import com.baidu.stumanager.dao.impl.TeacherImpl;

import com.baidu.stumanager.dao.impl.UserinfoImpl;

import com.baidu.stumanager.domain.Teacher;

import com.baidu.stumanager.domain.Userinfo;

public class Test1 {

public static void main(String[] args) {

TeacherImpl teacherImpl = new TeacherImpl();

UserinfoImpl userinfoImpl = new UserinfoImpl();

while(true){

System.out.println("欢迎使用蓝鸥老师管理系统v1.0beta");

System.out.println("0.登入...\n1.添加老师...\n2.修改老师...\n3.删除老师...\n4.查询所有老师...\n"

+ "5.退出系统...\n6.按ID查询信息...\n7.模糊查询..."

+ "\n8.分页查找...\n9. 注册...");

Scanner scanner = new Scanner(System.in);

int choice = scanner.nextInt();

switch (choice) {

case 0:

System.out.println("请输入账号...");

String userid = scanner.next();

System.out.println("请输入密码...");

String userpwd = scanner.next();

Userinfo userinfo = new Userinfo();

userinfo.setUserid(userid);

userinfo.setUserpwd(userpwd);

if (null == userinfoImpl.getUserinfoByIdAndPwd(userinfo)) {

System.out.println("登入失败");

} else {

System.out.println(userinfoImpl.getUserinfoByIdAndPwd(userinfo));

}

break;

case 1:

System.out.println("请输入老师工号...");

int tid = scanner.nextInt();

System.out.println("请输入老师姓名...");

String tname = scanner.next();

Teacher teacher = new Teacher();

teacher.setTid(tid);

teacher.setTname(tname);

if(teacherImpl.saveTeacher(teacher) > 0){

System.out.println("数据成功录入系统...");

}else{

System.out.println("对不起,数据录入失败...");

}

break;

case 2:

System.out.println("请输入要修改老师工号...");

tid = scanner.nextInt();

System.out.println("请输入老师修改后的姓名...");

tname = scanner.next();

teacher = new Teacher();

teacher.setTid(tid);

teacher.setTname(tname);

if ( teacherImpl.updateTeacher(teacher) > 0) {

System.out.println("修改成功！！");

} else {

System.out.println("修改失败");

}

break;

case 3:

System.out.println("请输入要删除的老师ID");

tid = scanner.nextInt();

if ( teacherImpl.deleteTeacher(tid) > 0) {

System.out.println("删除成功！！");

} else {

System.out.println("删除失败");

}

break;

case 4:

for (Teacher teacher1 : teacherImpl.getTeachers())

System.out.println(teacher1);

break;

case 5:

System.exit(0);

break;

case 6:

System.out.println("请输入老师工号...");

tid = scanner.nextInt();

if (null == teacherImpl.getTeachcerById(tid)) {

System.out.println("查询失败！");

} else {

System.out.println(teacherImpl.getTeachcerById(tid));

}

break;

case 7:

System.out.println("请输入老师姓...");

tname = scanner.next();

teacherImpl.getTeacherByName(tname);

if (null == teacherImpl.getTeacherByName(tname)) {

System.out.println("查询失败");

} else {

for (Teacher teacher2 : teacherImpl.getTeacherByName(tname)) {

System.out.println(teacher2);

}

}

break;

case 8:

System.out.println("请输入页数...");

int pageIndex = scanner.nextInt();

teacherImpl.getTeacherByPage(pageIndex);

break;

case 9:

System.out.println("请输入账号...");

userid = scanner.next();

System.out.println("请输入密码...");

userpwd = scanner.next();

System.out.println("请输入年龄...");

int age = scanner.nextInt();

userinfo = new Userinfo();

userinfo.setUserid(userid);

userinfo.setUserpwd(userpwd);

userinfo.setUserage(age);

if (userinfoImpl.register(userinfo)> 0) {

System.out.println("注册成功！！！");

}

break;

default:

break;

}

}

}

}

网址：老师（学生）信息管理系统（JDBC如何连接SQL） https://www.yuejiaxmz.com/news/view/425077

相关内容

养老院老人健康信息管理系统 /养老院管理系统
社区生活信息管理系统的设计与实现(SQL)
PowerBuilder连接SQLServer失败 SQL State：‘28000’
数据库 = JDBC,连接池对象
【健康管理系统——开题报告 分享（仅供参考呀）】
学生信息管理系统的设计与实现
学生信息管理系统用例图
微信小程序智能停车场管理系统+文档
生活中管理信息系统的应用
生活老师如何管理学生

随便看看