JDBC

2024-02-07 20:36| 来源: 网络整理| 查看: 265

JDBC（Java Data Base Connection）的作用是连接数据库

先看下jdbc连接SQLServer数据库的简单例子

代码实现（FirstJDBC)：

package com.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class FirstJDBC { public static void main(String[] args) { //调用连接数据库的操作 Connection con = createConnection(); } /** * JDBC 建立 SQL Server数据库连接 */ private static Connection createConnection() { //定义加载驱动程序 String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //定义连接服务器和数据库sample String dbURL = "jdbc:sqlserver://localhost:1433; DataBaseName = sample1" ; //默认用户名，不要用windows默认身份验证 String userName = "sa" ; String userPassword = "zhichao" ; Connection connection = null ; Statement sta = null ; try { //正式加载驱动 Class.forName(driverName); //开始连接 connection = DriverManager.getConnection(dbURL, userName, userPassword); System.out.println("Connection Success !"); //向数据库中执行SQL语句 sta = connection.createStatement() ; ResultSet rs = sta.executeQuery("SELECT id,name,height From Table_1"); while(rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); float height = rs.getFloat("height"); System.out.println("id = "+id+" name = "+name+" height = "+height); } } catch (Exception e) { System.out.println("Connection Fail !"); e.printStackTrace() ; } /** * 关闭数据库 * @param connection */ finally { try { if (null != sta) { sta.close() ; sta = null; System.out.println("Statement 关闭成功"); } if (null != connection) { connection.close() ; connection = null; System.out.println("Connection 关闭成功"); } } catch (Exception e) { e.printStackTrace() ; } } return connection ; } }

小结：

要写一个jdbc程序，先要加载相应数据库的驱动程序，驱动程序最好放在你建的工程里面，可以在你的工程下面建一个 lib文件夹以存储外部的jar文件，这样的话把你的工程拷贝到别的计算机运行，仍能成功执行。

jdbc代码一般步骤：

1）加载外部驱动程序（jar包）

2）正式加载驱动程序（Class.forName(driverName) ）

3）获取connection连接（在jdk中的sql包中，只提供了一个类那就是DriverManeger,通过调用它的静态方法getConnection(),可以得到以数据库的连接

4）创建sql语句的声明（Statement），执行sql语句（查询），遍历结果集

5）关闭数据库连接（一般用finally{}来处理，或者调用方法的形式来完成，关闭之前先判断你要关闭的对象连接是否为空，如果空那会抛异常，所以先判断）

------------------------------------- ------------------------------------- ------------------------Data Access Objects-------------------- ------------------------------------------- ---------------------------

使用 DAO模式来对数据库做增删改查操作

这种模式可以大概分为三个层：1.DAO层 2.服务层 3.表现层

1）表现层：相当于客户端用来查看，提交信息的角色

2）服务层：是表现层和DAO层的纽带，其实也没干什么事就是通知消息的角色

3）DAO ：真正要做事的角色（对数据库的某些操作）

举个生活中的例子：

就好比你去餐厅吃饭，你充当一个（表现层）的角色，然后有美女服务员（服务层），问你需要吃什么东西，给你下一张订单，让你填。之后服务员把订单传到厨师（DAO层）那里，具体操作厨师会搞定，一段时间后厨师把做好的食物传给服务员，服务员把食物在传给客户，这些操作就算基本完成了。

执行顺序：表现层-->服务层-->DAO层-->返回服务层-->返回表现层

来看看实现DAO模式的UML图：

代码实现：

1.Bean文件，在这主要作用(有点像中介存储的角色)：当从数据库拿出数据后，一个个set到该类里，进行赋值，然后把该对象放到集合中，之后再get出来

Student.java

package com.myjdbc.bean; public class Student { private Integer stuId; private String stuName ; private Integer stuAge; private String stuTel ; private String stuAddress ; private Integer groupId; public Integer getStuId() { return stuId; } public void setStuId(Integer stuId) { this.stuId = stuId; } public String getStuName() { return stuName; } public void setStuName(String stuName) { this.stuName = stuName; } public Integer getStuAge() { return stuAge; } public void setStuAge(Integer stuAge) { this.stuAge = stuAge; } public String getStuTel() { return stuTel; } public void setStuTel(String stuTel) { this.stuTel = stuTel; } public String getStuAddress() { return stuAddress; } public void setStuAddress(String stuAddress) { this.stuAddress = stuAddress; } public Integer getGroupId() { return groupId; } public void setGroupId(Integer groupId) { this.groupId = groupId; } }

2.java连接数据库的基本操作及关闭，封装在一个类中

JDBCUtils.java

package com.myjdbc.utils; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class JDBCUtils { /** * 获取连接 * */ public static Connection getConnection() { String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; String url = "jdbc:sqlserver://localhost:1433; DataBaseName = studentManager"; String user = "sa" ; String password = "zhichao"; Connection con = null ; try { Class.forName(driverName); con = DriverManager.getConnection(url, user, password); System.out.println("success"); } catch (Exception e) { e.printStackTrace(); } return con ; } /** * 关闭连接 */ public static void free(ResultSet rs, Statement sta , Connection con) { try { if(null != rs) { rs.close(); rs = null ; } if(null != sta) { sta.close(); sta = null ; } if(null != con) { con.close(); con = null ; } } catch (Exception e) { e.printStackTrace(); } } }

3.定义一个DAO接口

StudentDAO.java

package com.myjdbc.dao; import java.util.Set; import com.myjdbc.bean.Student ; public interface StudentDAO { public int addStudent(Student student) ; public int deleteStudent(String name); public int updateStudent(String name); public Student findStudent(String name); public Set findAll(); }

4.实现DAO接口的类，具体DAO,做重要工作的类

ConcreteStudentDao.java

package com.myjdbc.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashSet; import java.util.Set; import com.myjdbc.bean.Student; import com.myjdbc.dao.StudentDAO; import com.myjdbc.utils.JDBCUtils; public class ConcreteStudentDao implements StudentDAO{ //增加一个学生 public int addStudent(Student student) { Connection con = null ; PreparedStatement ps = null ; int i = 0 ; try { con = JDBCUtils.getConnection(); String sql = "insert into student(stuName,stuAge,stuTel,stuAddress,groupId) values(?,?,?,?,?)"; ps = con.prepareStatement(sql); ps.setString(1, student.getStuName()); ps.setInt(2, student.getStuAge()); ps.setString(3, student.getStuTel()); ps.setString(4, student.getStuAddress()); ps.setInt(5, student.getGroupId()); i = ps.executeUpdate() ; } catch(SQLException e) { throw new DAOException(e.getMessage(),e); } finally { JDBCUtils.free(null, ps, con); } return i; } //删除一个学生 public int deleteStudent(String name) { Connection con = null ; PreparedStatement ps = null ; int i = 0 ; try { con = JDBCUtils.getConnection(); String sql = "delete from student where stuName =?"; ps = con.prepareStatement(sql); ps.setString(1, name); i = ps.executeUpdate() ; } catch(SQLException e) { throw new DAOException(e.getMessage(),e); } finally { JDBCUtils.free(null, ps, con); } return i; } //修改一个学生 public int updateStudent(String name) { Connection con = null ; PreparedStatement ps = null ; int i = 0 ; try { con = JDBCUtils.getConnection(); String sql = "update student set stuAge=stuAge+1 where stuName =?"; ps = con.prepareStatement(sql); ps.setString(1, name); i = ps.executeUpdate() ; } catch(SQLException e) { throw new DAOException(e.getMessage(),e); } finally { JDBCUtils.free(null, ps, con); } return i; } //查询一行 public Student findStudent(String name) { Connection con = null ; PreparedStatement ps = null ; Student stu = null ; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "select stuName,stuAge,stuTel,stuAddress,groupId from student where stuName =?"; ps = con.prepareStatement(sql); ps.setString(1, name); rs = ps.executeQuery() ; stu = new Student(); while(rs.next()) { stu.setStuName(rs.getString(1)); stu.setStuAge(rs.getInt(2)); stu.setStuTel(rs.getString(3)); stu.setStuAddress(rs.getString(4)); stu.setGroupId(rs.getInt(5)); } } catch(SQLException e) { throw new DAOException(e.getMessage(),e); } finally { JDBCUtils.free(rs, ps, con); } return stu; } //查询所有 public Set findAll() { Connection con = null ; PreparedStatement ps = null ; Student stu = null ; ResultSet rs = null; Set set = null ; try { con = JDBCUtils.getConnection(); String sql = "select stuName,stuAge,stuTel,stuAddress,groupId from student"; ps = con.prepareStatement(sql); set = new HashSet() ; rs = ps.executeQuery() ; while(rs.next()) { stu = new Student(); stu.setStuName(rs.getString(1)); stu.setStuAge(rs.getInt(2)); stu.setStuTel(rs.getString(3)); stu.setStuAddress(rs.getString(4)); stu.setGroupId(rs.getInt(5)); set.add(stu); } } catch(SQLException e) { throw new DAOException(e.getMessage(),e); } finally { JDBCUtils.free(rs, ps, con); } return set; } }

5.自定义异常继承了运行时异常，具体操作让父类实现

DAOException.java

package com.myjdbc.dao; /** * 自定义异常 * @author Administrator * */ public class DAOException extends RuntimeException { public DAOException() { super(); } public DAOException(String messege,Throwable cause) { super(messege,cause); } public DAOException(String messege) { super(messege); } public DAOException(Throwable cause) { super(cause); } }

6定义一个服务类（服务层），本来还要定义一个接口，这里简写了，客户与DAO的纽带，持有DAO对象的引用

StudentService.java

package com.myjdbc.service; import java.util.Set; import com.myjdbc.bean.Student; import com.myjdbc.dao.StudentDAO; import com.myjdbc.dao.ConcreteStudentDao; public class StudentService { StudentDAO sd = new ConcreteStudentDao(); public int add(Student student) { return this.sd.addStudent(student); } public int delete(String name) { return this.sd.deleteStudent(name); } public int update(String name) { return this.sd.updateStudent(name); } public Student find(String name) { return this.sd.findStudent(name); } public Set findAll() { return this.sd.findAll(); } }

7.定义一个测试类，相当于（表现层）

Client.java

package com.myjdbc.test; import java.util.HashSet; import java.util.Iterator; import java.util.Set; import com.myjdbc.bean.Student; import com.myjdbc.service.StudentService; public class Client { public static void main(String[] args) { Student stu = new Student(); Set set = new HashSet(); // stu.setStuName("zhangsan"); // stu.setStuAge(20); // stu.setStuTel("18779157911"); // stu.setStuAddress("china"); // stu.setGroupId(1); StudentService ss = new StudentService(); //System.out.println(ss.add(stu)); //System.out.println(ss.delete("aa")); //System.out.println(ss.update("bb")); //stu = ss.find("cc"); //System.out.println(stu.getStuName() +" " +stu.getStuAge()+" "+stu.getStuTel()+" "+stu.getStuAddress()+" "+stu.getGroupId()); set = ss.findAll() ; Iterator iterator = set.iterator(); while(iterator.hasNext()) { Student student = (Student)iterator.next() ; System.out.println(student.getStuName() +" " +student.getStuAge()+" "+student.getStuTel()+" "+student.getStuAddress()+" "+student.getGroupId()); } } }

【本文地址】

公司简介

联系我们