一、下载数据库JDBC驱动
1、1:Mysql驱动
https://dev.mysql.com/downloads/connector/
下载得到的是一个压缩包,解压后到里面寻找文件:mysql-connector-java-8.0.12.jar
这个:mysql-connector-java-8.0.12.jar就是我们要导入到jJAVA项目中。
二、导入数据库驱动包
这次我是应用Intellje IDEA开发软件,如果应用eclipse直接把包复制到项目中的lib中即可。
或者:
找到你的包所在位置
然后点击Apply按钮后点击OK
结果:
三、代码实现
1、建包
建类:
package com.yangwansheng.test.util; import java.sql.*; public class MysqlJdbcUtil { private static String driver = "com.mysql.jdbc.Driver";//驱动 //数据库 private static String url = "jdbc:mysql://localhost:3306/worksql?useUnicode=true&characterEncoding=UTF-8"; private static String user = "YangWanSheng";//用户名 private static String pwd = "Wansheng";//密码 private static Connection conn = null;//连接对象 private static Statement stat = null;//执行语句对象 //打开数据库连接 public static void open() { try { //加载驱动 Class.forName("com.mysql.jdbc.Driver"); //打开连接--实例化数据库连接对象 conn = DriverManager.getConnection(url, user, pwd); } catch (Exception e) { e.printStackTrace(); } } //关闭数据库连接对象,释放内存 public static void close() { try { //判断数据库是否处于连接中 if (conn != null && conn.isClosed()) { conn.close(); } if (stat != null) { stat.close(); } } catch (Exception e) { e.printStackTrace(); } } /*** * 查询所有数据 */ public static ResultSet executeQuery(String sql) { try { open(); stat = conn.createStatement(); return stat.executeQuery(sql); } catch (Exception e) { e.printStackTrace(); } return null; } /** * 条件查询、分页查询 * @param sql 数据库执行语句 * @param o 参数 * @return 反回一个集合命令 */ public static ResultSet executeQuery(String sql,Object... o){ try { open(); PreparedStatement pst=conn.prepareStatement(sql); for(int i=0;i<o.length;i++) pst.setObject(i+1, o[i]); return pst.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } return null; } /**执行增、删、改*/ public static int executUpeate(String sql, Object... obj) { try { open(); PreparedStatement pst = conn.prepareStatement(sql); for (int i = 0; i < obj.length; i++) { pst.setObject(i + 1, obj[i]); } stat = pst; return pst.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } return 0; } }
四、jdbcUtil类的调用
4、1:创建bo实体类
package com.yangwansheng.test.bo; public class StudentCourse { private int id; private String Sno; private String Cno; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getSno() { return Sno; } public void setSno(String sno) { Sno = sno; } public String getCno() { return Cno; } public void setCno(String cno) { Cno = cno; } public StudentCourse(int id, String sno, String cno) { this.id = id; Sno = sno; Cno = cno; } public StudentCourse(){} @Override public String toString() { return "StudentCourse{" + "id=" + id + ", Sno=\'" + Sno + \'\\'\' + ", Cno=\'" + Cno + \'\\'\' + \'}\'; } }
4、2:数据库访问层DAO
package com.yangwansheng.test.dao; import com.yangwansheng.test.bo.StudentCourse; import com.yangwansheng.test.util.MysqlJdbcUtil; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; public class StudentCourseDao { /**查询所有的学生选课表的信息*/ public List<StudentCourse> getAllStudentCourse(){ List<StudentCourse> list = new ArrayList<>(); String sql = "SELECT `StudentCourse`.id, `Course`.Cname, `Student`.Sname FROM `StudentCourse`"; sql +=" LEFT JOIN `Course` ON `StudentCourse`.Cno = `Course`.Cno "; sql +=" LEFT JOIN `Student` ON `StudentCourse`.Sno = `Student`.Sno LIMIT 0,5"; ResultSet rs = MysqlJdbcUtil.executeQuery(sql); try{ while (rs.next()){ StudentCourse stucou = new StudentCourse( rs.getInt("id"),rs.getString("Cname"), rs.getString("Sname") ); list.add(stucou); } return list; }catch (Exception e){ e.printStackTrace(); }finally { MysqlJdbcUtil.close(); } return null; } /** * 分页查询 * @param state * @param end * @return */ public List<StudentCourse> getPageStudentCourse(int state, int end){ List<StudentCourse> list = new ArrayList<>(); String sql = "SELECT `StudentCourse`.id, `Course`.Cname, `Student`.Sname FROM `StudentCourse`"; sql +=" LEFT JOIN `Course` ON `StudentCourse`.Cno = `Course`.Cno "; sql +=" LEFT JOIN `Student` ON `StudentCourse`.Sno = `Student`.Sno LIMIT ?,?"; ResultSet rs = MysqlJdbcUtil.executeQuery(sql,state,end); try{ while (rs.next()){ StudentCourse stucou = new StudentCourse( rs.getInt("id"),rs.getString("Cname"), rs.getString("Sname") ); list.add(stucou); } return list; }catch (Exception e){ e.printStackTrace(); }finally { MysqlJdbcUtil.close(); } return null; } //修改 public int updateSc(int id ,String Cno){ String sql ="update StudentCourse set Cno = ? where id=? "; int i = MysqlJdbcUtil.executUpeate(sql,Cno,id); return i; } /** * 添加 * @return 大于一添加成功 */ public int addSc(String sno, String con){ String sql ="insert into StudentCourse(Sno,Cno) VALUES(?,?);"; int i = MysqlJdbcUtil.executUpeate(sql,sno,con); return i; } /** * 删除 * @param id * @return 大于1执行成功 */ public int delSc(int id){ String sql ="delete from StudentCourse where id=?;"; int i = MysqlJdbcUtil.executUpeate(sql,id); return i; } }
4、3:创建测试类
package com.yangwansheng.test.test; import com.yangwansheng.test.bo.StudentCourse; import com.yangwansheng.test.dao.StudentCourseDao; public class MysqlTest { public static void main(String[] args) { StudentCourseDao SCdao = new StudentCourseDao(); System.out.println(SCdao.getAllStudentCourse()); } }
结果: