JDBC入门

来源：互联网发布：tomcat连不上mysql 编辑：程序博客网时间：2024/06/07 02:44

JDBC
（Java Database Connectivity） Java数据库连接，用java操作数据库的一门技术，一组规范，接口。

五个步骤

1.加载驱动

2.创建连接

3.建立通道

4.执行并返回结果集

5.关闭

这里写图片描述

一.数据库层
1.创建表

create table category(    cid number(10) primary key,    cname varchar2(100),    cdesc   varchar2(200))

2.创建序列

create sequence seq_category minvalue 0 start with 0 increment by 1

二.VO(Value Object)实体层
创建category类，实现getter/setter方法

public class Category {    private Integer cid;    private String  name;    private String cdesc;}

三.连接数据库的工具类 ConnOracle

public class ConnOracle2 {    private static Connection conn;    private static final String className="oracle.jdbc.OracleDriver";    private static final String url="jdbc:oracle:thin:@localhost:1521:ORCL11";    private static final String user="scott";    private static final String password="tiger";    //1,加载驱动(可以静态加载)    static{        //oracle.jdbc.OracleDriver.class        try {            Class.forName(className);        } catch (ClassNotFoundException e) {            e.printStackTrace();            System.out.println("类没找到");        }    }    public static Connection getConnection() {        //2,创建连接        try {            conn = DriverManager.getConnection(url, user, password);        } catch (SQLException e) {            e.printStackTrace();            System.out.println("数据库连接失败!");        }        return conn;    }    public static void free(Connection conn,Statement statement,ResultSet executeQuery){        try {            if (executeQuery != null) {                executeQuery.close();            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            try {                if (statement!= null) {                    statement.close();                }            } catch (SQLException e) {                e.printStackTrace();            } finally {                try {                    if (conn != null) {                        conn.close();                    }                } catch (SQLException e) {                    e.printStackTrace();                }            }        }    }}

四.DAO(Data Access Object) 数据访问层

对表的最基本的CRUD
接口：

public interface CategoryDaoImpl {    /**     * 添加一条     * @param category     */    public void addCategory(Category category);    /**     * 更新一条     * @param category     */    public void updateCategory(Category category);    /**     * 删除一条     * @param category     */    public void deleteCategoryById(int id);    /**     * 查一条     * @param id     * @return     */    public Category selecCategoryById(int id);    /**     * 查所有     * @return     */    public List<Category> selecCategory();}

接口的实现类

public class CategoryDao implements CategoryDaoImpl {    Connection conn = null;    public CategoryDao() {        // 3,建立通道        conn = ConnOracle2.getConnection();    }    @Override    public void addCategory(Category category) {        String sql = "INSERT INTO CATEGORY VALUES(seq_category.nextval,?,?)";        PreparedStatement prepareStatement = null;        try {            prepareStatement = conn.prepareStatement(sql);            prepareStatement.setString(1, category.getName());            prepareStatement.setString(2, category.getCdesc());            // 4,执行sql            int count = prepareStatement.executeUpdate();            if (count >= 1) {                System.out.println("插入成功！");            } else {                System.out.println("插入失败！");            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            // 5.关闭            ConnOracle2.free(conn, prepareStatement, null);        }    }    @Override    public void updateCategory(Category category) {        // 3,建立通道        String sql = "UPDATE CATEGORY SET cname=?,cdesc=? WHERE cid=?";        PreparedStatement prepareStatement = null;        try {            prepareStatement = conn.prepareStatement(sql);            prepareStatement.setString(1, category.getName());            prepareStatement.setString(2, category.getCdesc());            prepareStatement.setInt(3, category.getCid());            // 4,执行sql            int count = prepareStatement.executeUpdate();            if (count >= 1) {                System.out.println("更新成功！");            } else {                System.out.println("更新失败！");            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            // 5.关闭            ConnOracle2.free(conn, prepareStatement, null);        }    }    @Override    public void deleteCategoryById(int id) {        String sql = "DELETE FROM CATEGORY WHERE cid=?";        PreparedStatement prepareStatement = null;        try {            prepareStatement = conn.prepareStatement(sql);            prepareStatement.setInt(1, id);            // 4,执行sql            int count = prepareStatement.executeUpdate();            if (count >= 1) {                System.out.println("删除成功！");            } else {                System.out.println("删除失败！");            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            // 5.关闭            ConnOracle2.free(conn, prepareStatement, null);        }    }    @Override    public Category selecCategoryById(int id) {        String sql = "SELECT * FROM CATEGORY WHERE cid=?";        PreparedStatement prepareStatement = null;        Category category = null;        ResultSet executeQuery = null;        try {            prepareStatement = conn.prepareStatement(sql);            prepareStatement.setInt(1, id);            // 4,执行sql            executeQuery = prepareStatement.executeQuery();            if (executeQuery.next()) {                category = new Category();                category.setCid(executeQuery.getInt("cid"));                category.setName(executeQuery.getString("cname"));                category.setCdesc(executeQuery.getString("cdesc"));            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            // 5.关闭            ConnOracle2.free(conn, prepareStatement, executeQuery);        }        return category;    }    @Override    public List<Category> selecCategory() {        // 3,建立通道        String sql = "SELECT * FROM CATEGORY";        PreparedStatement prepareStatement = null;        List<Category> list = new ArrayList<>();        ResultSet executeQuery = null;        try {            prepareStatement = conn.prepareStatement(sql);            // 4,执行sql            executeQuery = prepareStatement.executeQuery();            while (executeQuery.next()) {                Category category = new Category();                category.setCid(executeQuery.getInt("cid"));                category.setName(executeQuery.getString("cname"));                category.setCdesc(executeQuery.getString("cdesc"));                list.add(category);            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            // 5.关闭            ConnOracle2.free(conn, prepareStatement, executeQuery);        }        return list;    }}

0 0