jdbc学习

来源：互联网发布：淘宝二手收购编辑：程序博客网时间：2024/06/06 14:10

前言：在任何一款应用中，大都不可能避开对数据库的操作。所以熟练掌握数据库操作是非常有必要的，JDBC相关知识一定要好好掌握，笔者之前就没有好好学扎实JDBC，学会了增删改查之后便把这些放下，直接奔Hibernate这些ORM框架去了，导致很多东西都只是会用而不明其中原理，也对框架没有一个很好的掌握。作为一名程序员，只知道照着别人的东西去写代码只知道去用框架却不明白其中原理是非常可悲的。所以笔者又重新回来好好看了一遍JDBC相关的知识。还是那句话，学技术，一定不要浮躁，要脚踏实地，一步一个脚印慢慢来，好好把基础打扎实，不要急着去追求那些所谓的高大上的技术。要知道万丈高楼平地起。
好了，废话说了这么多，下面开始进入正题。
一.JDBC
JDBC（Java Data Base Connectivity,Java数据库连接），由一些接口和类构成的API。JDBC是J2SE的一部分。
应用程序通过JDBC来访问数据库。
要使用JDBC，我们首先需要注册JDBC，这个过程我们只需做一次，所以我们把注册JDBC这部分代码放入static代码块中

static {        try {            Class.forName(driver);        } catch (Exception e) {            // TODO: handle exception            System.out.println("Exception:" + e.getMessage() + "");            throw new ExceptionInInitializerError(e);        }    }

当我们注册驱动后，我们需要去建立与数据库的链接

public static Connection getConnection() {        Connection conn = null;        try {            conn = DriverManager.getConnection(url, user, password);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return conn;    }

之后便可在java代码块中创建sql语句对数据库内容进行操作，并对结果集进行操作，最后一定要关闭资源，数据库资源是非常宝贵的。

下面给出DbHelper的完全代码：

package util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DbHelper {    private static final String driver = "oracle.jdbc.driver.OracleDriver";    private static final String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL";    private static final String user = "SCOTT";    private static final String password = "cong960227";    static {        try {            Class.forName(driver);        } catch (Exception e) {            // TODO: handle exception            System.out.println("Exception:" + e.getMessage() + "");            throw new ExceptionInInitializerError(e);        }    }    public static Connection getConnection() {        Connection conn = null;        try {            conn = DriverManager.getConnection(url, user, password);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return conn;    }    public static void free(ResultSet rs, Statement st, Connection conn) {        if (rs != null) {            try {                rs.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }        if (st != null) {            try {                st.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }        if (conn != null) {            try {                conn.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }    }}

二.利用JDBC执行CURD操作
首先我们新建一个实体类User

package entity;import java.io.Serializable;import java.util.Date;public class User implements Serializable {    /**     *      */    private static final long serialVersionUID = 1L;    private Integer id;    private String name;    private Date birthday;    private float salary;    public User() {    }    public User(Integer id, String name, Date birthday, float salary) {        this.id = id;        this.name = name;        this.birthday = birthday;        this.salary = salary;    }    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public Date getBirthday() {        return birthday;    }    public void setBirthday(Date birthday) {        this.birthday = birthday;    }    public float getSalary() {        return salary;    }    public void setSalary(float salary) {        this.salary = salary;    }    public String toString() {        return "[id=" + id + ",name=" + name + ",birthday=" + birthday                + ",salary=" + salary + "]";    }}

接下来看Dao层
BaseDao

package dao;import entity.User;public interface BaseDao {    public boolean add();    public boolean update();    public User getById(Integer id);    public boolean delete();}

UserDao

package dao;import entity.User;public interface UserDao extends BaseDao {    public boolean add();    public boolean update();    public User getById(Integer id);    public boolean delete();}

UserDaoImpl

package dao.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import util.DbHelper;import dao.UserDao;import entity.User;public class UserDaoImpl implements UserDao {    @Override    public boolean add() {        // TODO Auto-generated method stub        return false;    }    @Override    public boolean update() {        // TODO Auto-generated method stub        return false;    }    @Override    public User getById(Integer id) {        // TODO Auto-generated method stub        Connection connection = null;        ResultSet rs = null;        PreparedStatement ps = null;        User user = new User();        try {            connection = DbHelper.getConnection();            String sql = "select id,name,birthday,salary from wangcc_user where id=?";            ps = connection.prepareStatement(sql);            ps.setInt(1, id);            rs = ps.executeQuery();            while (rs.next()) {                user.setId(rs.getInt(1));                user.setName(rs.getString(2));                user.setBirthday(rs.getDate(3));                user.setSalary(rs.getFloat(4));            }        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        if (user != null) {            return user;        }        return null;    }    @Override    public boolean delete() {        // TODO Auto-generated method stub        return false;    }}

DaoFactory

package factory;import java.util.Properties;import trying.PropertiesUtil;import dao.BaseDao;import dao.UserDao;public class Daofactory {    private static final String dao_config = "src/dao_config.properties";    public static UserDao getUserdao() {        Class<?> clazz;        UserDao userDao = null;        Properties prop = PropertiesUtil                .getProperties("src/dao_config.properties");        String classname = prop.getProperty("userDao");        try {            clazz = Class.forName(classname);            userDao = (UserDao) clazz.newInstance();        } catch (Exception e) {            // TODO: handle exception        }        return userDao;    }    public static String getClassName(String className) {        Properties prop = PropertiesUtil.getProperties(dao_config);        return prop.getProperty(className);    }    public static BaseDao getDao(String className) {        BaseDao baseDao = null;        String name = getClassName(className);        try {            Class<?> clazz = Class.forName(name);            baseDao = (BaseDao) clazz.newInstance();        } catch (Exception e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return baseDao;    }}

PropertiesUtil见http://blog.csdn.net/BryantLmm/article/details/53327474
dao_config.properties为：通过属性文件来操作，这个有点Spring配置的意思，思想是一致的。

userDao=dao.impl.UserDaoImpl

然后上测试代码：

package test;import java.sql.Connection;import java.sql.Date;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import org.junit.Test;import service.UserService;import util.DbHelper;import dao.UserDao;import entity.User;import factory.Daofactory;public class JDBCTest {    public static void main(String[] args) {        // TODO Auto-generated method stub        query();    }    @Test    public static void query() {        // Class<?> clazz;        // UserDao userDao = null;        // try {        // clazz = Class.forName("dao.impl.UserDaoImpl");        // userDao = (UserDao) clazz.newInstance();        // } catch (Exception e) {        // // TODO Auto-generated catch block        // e.printStackTrace();        // }        UserDao userDao = (UserDao) Daofactory.getDao("userDao");        // UserDaoImpl userDao = new UserDaoImpl();        UserService userService = new UserService(userDao);        User user = userService.getUser(1);        System.out.println(user.toString());    }    public static void testaddBatch() {        Connection conn = null;        ResultSet rs = null;        PreparedStatement ps = null;        try {            conn = DbHelper.getConnection();            String sql = "insert into wangcc_user (name,birthday,salary) values(?,?,?)";            ps = conn.prepareStatement(sql);            for (int i = 0; i < 20; i++) {                ps.setString(1, "james");                ps.setDate(2, new Date(System.currentTimeMillis()));                ps.setFloat(3, 15000);                ps.addBatch();            }            ps.executeBatch();        } catch (SQLException e) {            // TODO: handle exception        } finally {            DbHelper.free(rs, ps, conn);        }    }}

0 0