快速入门

         //1.导入jar包
        //2.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //3.获取连接 url: jdbc:mysql://ip地址(域名):端口号/数据库名称 
        Connection con = DriverManager.getConnection("url","username","password");

        //4.获取执行者对象
        Statement stat = con.createStatement();

        //5.执行sql语句，并且接收结果
        String sql = "SELECT * FROM user";
        ResultSet rs = stat.executeQuery(sql);

        //6.处理结果
        while(rs.next()) {
            System.out.println(rs.getInt("id") + "\t" + rs.getString("name"));
        }

       //7.释放资源
        con.close();
        stat.close();
        con.close();

使用JDBC完成数据库的增删改查操作

数据准备

-- 创建db14数据库
CREATE DATABASE db14;

-- 使用db14数据库
USE db14;


-- 创建student表
CREATE TABLE student(
 id INT PRIMARY KEY AUTO_INCREMENT,    -- 学生id
 NAME VARCHAR(20),            -- 学生姓名
 age INT,                -- 学生年龄
 birthday DATE                -- 学生生日
);

-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,'1999-09-23'),(NULL,'李四',24,'1998-08-10'),
(NULL,'王五',25,'1996-06-06'),(NULL,'赵六',26,'1994-10-20');

代码模块

JavaBean

/**
这里采用的是包装类，原因是数据库里面可能存在null值，null是无法赋值给基本引用类型的
*/
//创建student实体类 
public class student implements Serializable{
    private Intager id;
    private String name;
    private Intager age;
    private Date birthday;
//有参无参方法，set和get方法
    }

StudentDao

public interface StudentDao {
     //查询所有学生信息
    ArrayList<Student> findAll();

     //根据id查询 返回值是一个学生对象 
    Student findById(Intager id);

     //增加一条信息
    int insert(Student stu);

     // 修改学生信息
    int update(Student stu);

     //删除学生信息
    int delete(Intager id);

}

StudentDaoImpl

@Repository("StudentDao")
publice  class StudentDaoImpl implements StudentDao{

/*
        查询所有学生信息
     */
    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;
        try{

            con = JDBCUtils.getConnection();

           //3.获取执行者对象
           stat = con.createStatement();

           //4.执行sql语句，并且接收返回的结果集
           String sql = "SELECT * FROM student";
           rs = stat.executeQuery(sql);

           //5.处理结果集
           while(rs.next()) {
               Integer sid = rs.getInt("sid");
               String name = rs.getString("name");
               Integer age = rs.getInt("age");
               Date birthday = rs.getDate("birthday");

               //封装Student对象
               Student stu = new Student(sid,name,age,birthday);

               //将student对象保存到集合中
               list.add(stu);
           }

       } catch(Exception e) {
           e.printStackTrace();
       } finally {
           //6.释放资源
           JDBCUtils.close(con,stat,rs);
       }
        //将集合对象返回
        return list;
    }

    /*
        条件查询，根据id查询学生信息
     */
    @Override
    public Student findById(Integer id) {
        Student stu = new Student();
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;
        try{

            con = JDBCUtils.getConnection();

            //3.获取执行者对象
            stat = con.createStatement();

            //4.执行sql语句，并且接收返回的结果集
            String sql = "SELECT * FROM student WHERE sid='"+id+"'";
            rs = stat.executeQuery(sql);

            //5.处理结果集
            while(rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");

                //封装Student对象
                stu.setSid(sid);
                stu.setName(name);
                stu.setAge(age);
                stu.setBirthday(birthday);
            }

        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            JDBCUtils.close(con,stat,rs);
        }
        //将对象返回
        return stu;
    }

    /*
        添加学生信息
     */
    @Override
    public int insert(Student stu) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        try{
            con = JDBCUtils.getConnection();

            //3.获取执行者对象
            stat = con.createStatement();

            //4.执行sql语句，并且接收返回的结果集
            Date d = stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(d);
            String sql = "INSERT INTO student VALUES ('"+stu.getSid()+"','"+stu.getName()+"','"+stu.getAge()+"','"+birthday+"')";
            result = stat.executeUpdate(sql);

        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            JDBCUtils.close(con,stat);
        }
        //将结果返回
        return result;
    }

    /*
        修改学生信息
     */
    @Override
    public int update(Student stu) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        try{
            con = JDBCUtils.getConnection();

            //3.获取执行者对象
            stat = con.createStatement();

            //4.执行sql语句，并且接收返回的结果集
            Date d = stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(d);
            String sql = "UPDATE student SET sid='"+stu.getSid()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+birthday+"' WHERE sid='"+stu.getSid()+"'";
            result = stat.executeUpdate(sql);

        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            JDBCUtils.close(con,stat);
        }
        //将结果返回
        return result;
    }

    /*
        删除学生信息
     */
    @Override
    public int delete(Integer id) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        try{
            con = JDBCUtils.getConnection();

            //3.获取执行者对象
            stat = con.createStatement();

            //4.执行sql语句，并且接收返回的结果集
            String sql = "DELETE FROM student WHERE sid='"+id+"'";
            result = stat.executeUpdate(sql);

        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            JDBCUtils.close(con,stat);
        }
        //将结果返回
        return result;
    }

}

StudentService

publiu interface StudentService{
     //查询所有学生信息
    ArrayList<Student> findAll();

     //根据id查询 返回值是一个学生对象 
    Student findById(Intager id);

     //增加一条信息
    int insert(Student stu);

     // 修改学生信息
    int update(Student stu);

     //删除学生信息
    int delete(Intager id);

}

StudentServiceImpl

@Service("StudentService")
public class StudentServiceImpl implements StudentServie{
     @Autowired
      StudentService studntService;

       @Override    
       public ArrayList<Student> findAll(){
            return studentService.findAll;
         }

     //根据id查询 返回值是一个学生对象 
    Student findById(Intager id);

     //增加一条信息
    int insert(Student stu);

     // 修改学生信息
    int update(Student stu);

     //删除学生信息
    int delete(Intager id);
}

StudentController

JDBCUtils

/*
    JDBC工具类
 */
public class JDBCUtils {
    //1.私有构造方法
    private JDBCUtils(){}

    //2.声明所需要的配置变量
    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;
    private static Connection con;

    //3.提供静态代码块。读取配置文件的信息为变量赋值，注册驱动
    static{
        try {
            //读取配置文件的信息为变量赋值
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("config.properties");
            Properties prop = new Properties();
            prop.load(is);

            driverClass = prop.getProperty("driverClass");
            url = prop.getProperty("url");
            username = prop.getProperty("username");
            password = prop.getProperty("password");

            //注册驱动
            Class.forName(driverClass);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //4.提供获取数据库连接方法
    public static Connection getConnection() {
        try {
            con = DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return con;
    }

    //5.提供释放资源的方法
    public static void close(Connection con, Statement stat, ResultSet rs) {
        if(con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(stat != null) {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //6.释放  连接 和 statement
    public static void close(Connection con, Statement stat) {
        if(con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(stat != null) {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

config.properties

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.59.129:3306/db14
username=root
password=root