java.sql.DriverManager用来装载驱动程序,获取数据库连接。
java.sql.Connection完成对某一指定数据库的联接
java.sql.Statement在一个给定的连接中作为SQL执行声明的容器,他包含了两个重要的子类型。
Java.sql.PreparedSatement 用于执行预编译的sql声明
Java.sql.CallableStatement用于执行数据库中存储过程的调用
java.sql.ResultSet对于给定声明取得结果的途径
示例代码——连接mysql:
java.sql.Connection完成对某一指定数据库的联接
java.sql.Statement在一个给定的连接中作为SQL执行声明的容器,他包含了两个重要的子类型。
Java.sql.PreparedSatement 用于执行预编译的sql声明
Java.sql.CallableStatement用于执行数据库中存储过程的调用
java.sql.ResultSet对于给定声明取得结果的途径
示例代码——连接mysql:
- package cn.itcast.cd.jsp.domain;
- import java.util.Date;
- public class Student {
- private int id;
- private String name;
- private String sex;
- private int age;
- private Date birthday;
- public int getId() {
- return id;
- }
- public void setId(int id) {
- this.id = id;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public String getSex() {
- return sex;
- }
- public void setSex(String sex) {
- this.sex = sex;
- }
- public int getAge() {
- return age;
- }
- public void setAge(int age) {
- this.age = age;
- }
- public Date getBirthday() {
- return birthday;
- }
- public void setBirthday(Date birthday) {
- this.birthday = birthday;
- }
- @Override
- public String toString() {
- return "Student [id=" + id + ", name=" + name + ", sex=" + sex
- + ", age=" + age + ", birthday=" + birthday + "]";
- }
- }
- package cn.itcast.cd.utils;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- /**
- * 操作JDBC的工具类
- * @author dendy
- * @version 1.0
- */
- public class DbUtils {
- private static String url = "jdbc:mysql://localhost/jdbc";
- private static String user = "root";
- private static String password = "admin";
- private DbUtils(){ //不允许工具类拥有实例对象
- }
- //只需要一份驱动,所以放到静态代码块中
- static {
- try {
- //利用Class.forName调用com.mysql.jdbc.Driver的静态代码块,得到一个Driver对象的实例,而不需要手动创建.
- Class.forName("com.mysql.jdbc.Driver");
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- }
- }
- /**
- * 获得数据库连接
- * @return 返回Jave.sql.Connection实例,发生异常返回null.
- */
- public static Connection getConnection(){
- try {
- Connection connection = DriverManager.getConnection(url, user, password);
- return connection;
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return null;
- }
- /**
- * 关闭Connection、Statement、ResultSet对象.
- * @param connection
- * @param statement
- * @param resultSet
- */
- public static void close(Connection connection, Statement statement, ResultSet resultSet){
- close(connection);
- close(statement);
- close(resultSet);
- }
- /**
- * 关闭Connection、Statement对象.
- * @param connection
- * @param statement
- */
- public static void close(Connection connection, Statement statement){
- close(connection);
- close(statement);
- }
- /**
- * 关闭Connection对象
- * @param connection
- */
- public static void close(Connection connection){
- try {
- connection.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- /**
- * 关闭Statement对象
- * @param statement
- */
- public static void close(Statement statement){
- try {
- statement.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- /**
- * 关闭ResultSet对象
- * @param resultSet
- */
- public static void close(ResultSet resultSet){
- try {
- resultSet.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- package cn.itcast.dao.Impl;
- import java.sql.Connection;
- import java.sql.Date;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.ArrayList;
- import java.util.List;
- import cn.itcast.cd.jsp.dao.IStudentDao;
- import cn.itcast.cd.jsp.domain.Student;
- import cn.itcast.cd.utils.DbUtils;
- public class StudentDao implements IStudentDao {
- @Override
- public void add(Student stu) {
- // 连接数据库
- Connection connection = DbUtils.getConnection();
- PreparedStatement preparedStatement = null;
- String sql = "INSERT INTO student VALUES (null,?,?,?,?)";
- try {
- preparedStatement = connection.prepareStatement(sql);
- preparedStatement.setString(1, stu.getName());
- preparedStatement.setString(2, stu.getSex());
- preparedStatement.setInt(3, stu.getAge());
- // 第二参数为java.sql.Date类型,为java.util.Date的子类,需要处理
- preparedStatement.setDate(4, new Date(stu.getBirthday().getTime()));
- preparedStatement.executeUpdate();
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- // 关闭资源
- DbUtils.close(connection, preparedStatement);
- }
- }
- @Override
- public void delete(int id) {
- // 连接数据库
- Connection connection = DbUtils.getConnection();
- PreparedStatement preparedStatement = null;
- String sql = "DELETE FROM student WHERE id = " + id;
- try {
- preparedStatement = connection.prepareStatement(sql);
- preparedStatement.executeUpdate();
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- // 关闭资源
- DbUtils.close(connection, preparedStatement);
- }
- }
- @Override
- public void update(int id, Student stu) {
- // 连接数据库
- Connection connection = DbUtils.getConnection();
- PreparedStatement preparedStatement = null;
- String sql = "UPDATE student SET name=?,sex=?,age=?,birthday=? WHERE ID="
- + id;
- try {
- preparedStatement = connection.prepareStatement(sql);
- preparedStatement.setString(1, stu.getName());
- preparedStatement.setString(2, stu.getSex());
- preparedStatement.setInt(3, stu.getAge());
- preparedStatement.setDate(4, new Date(stu.getBirthday().getTime()));
- preparedStatement.executeUpdate();
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- // 关闭资源
- DbUtils.close(connection, preparedStatement);
- }
- }
- @Override
- public Student get(int id) {
- // 连接数据库
- Connection connection = DbUtils.getConnection();
- ResultSet resultSet = null;
- PreparedStatement preparedStatement = null;
- String sql = "SELECT * FROM student WHERE ID=" + id;
- try {
- preparedStatement = connection.prepareStatement(sql);
- resultSet = preparedStatement.executeQuery();
- if (resultSet.next()) {
- Student stu = new Student();
- stu.setName(resultSet.getString("name"));
- stu.setSex(resultSet.getString("sex"));
- stu.setAge(resultSet.getInt("age"));
- stu.setBirthday(resultSet.getDate("birthday"));
- stu.setId(resultSet.getInt("id"));
- return stu;
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- // 关闭资源
- DbUtils.close(connection, preparedStatement, resultSet);
- }
- return null;
- }
- @Override
- public List<Student> getAll() {
- List<Student> stuList = new ArrayList<Student>();
- // 连接数据库
- Connection connection = DbUtils.getConnection();
- ResultSet resultSet = null;
- PreparedStatement preparedStatement = null;
- String sql = "SELECT * FROM student";
- try {
- preparedStatement = connection.prepareStatement(sql);
- resultSet = preparedStatement.executeQuery();
- while (resultSet.next()) {
- Student stu = new Student();
- stu.setName(resultSet.getString("name"));
- stu.setSex(resultSet.getString("sex"));
- stu.setAge(resultSet.getInt("age"));
- stu.setBirthday(resultSet.getDate("birthday"));
- stu.setId(resultSet.getInt("id"));
- stuList.add(stu);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- // 关闭资源
- DbUtils.close(connection, preparedStatement, resultSet);
- }
- return stuList;
- }
- @Override
- public List<Student> get(String name) {
- List<Student> stuList = new ArrayList<Student>();
- // 连接数据库
- Connection connection = DbUtils.getConnection();
- ResultSet resultSet = null;
- PreparedStatement preparedStatement = null;
- String sql = "SELECT * FROM student WHERE name='"+name+"'";
- try {
- preparedStatement = connection.prepareStatement(sql);
- resultSet = preparedStatement.executeQuery();
- while (resultSet.next()) {
- Student stu = new Student();
- stu.setName(resultSet.getString("name"));
- stu.setSex(resultSet.getString("sex"));
- stu.setAge(resultSet.getInt("age"));
- stu.setBirthday(resultSet.getDate("birthday"));
- stu.setId(resultSet.getInt("id"));
- stuList.add(stu);
- }
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- // 关闭资源
- DbUtils.close(connection, preparedStatement, resultSet);
- }
- return stuList;
- }
- }