JDBC访问Mysql数据库

新建一个maven工程，用的spring框架

1. 在pom.xml中天街mysql的依赖

1.1 版本号管理

  <properties>    <kinpy-domain.version>1.0.0-SNAPSHOT</kinpy-domain.version>    <kinpy-common.version>1.0.0-SNAPSHOT</kinpy-common.version>    <junit-version>4.4</junit-version>    <java.version>1.7</java.version>    <guava.version>19.0</guava.version>    <java.encoding>GBK</java.encoding>    <spring.version>2.5.6</spring.version>    <mysql.version>5.1.44</mysql.version>  </properties>

1.2 添加jar包依赖

<dependency>    <groupId>mysql</groupId>    <artifactId>mysql-connector-java</artifactId>    <version>${mysql.version}</version></dependency><dependency>    <groupId>commons-lang</groupId>    <artifactId>commons-lang</artifactId>    <version>2.4</version></dependency>

2. 新建spring-jdbc-ds.xml, 创建datasource实例bean，配置db服务链接，用户密码

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"       xsi:schemaLocation="http://www.springframework.org/schema/beans                               http://www.springframework.org/schema/beans/spring-beans.xsd">    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>        <property name="url" value="jdbc:mysql://localhost:3306/mydbname"/>        <property name="username" value="xxxxx"/>        <property name="password" value="xxxxx"/>    </bean></beans>

3. 数据模型类：

package galaxy.com.db.model;public class UserDO {    private Integer id;    private String name;    private Integer age;    private String job;    /**     * Getter method for property <tt>id</tt>.     *     * @return property value of id     */    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    /**     * Getter method for property <tt>name</tt>.     *     * @return property value of name     */    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    /**     * Getter method for property <tt>age</tt>.     *     * @return property value of age     */    public Integer getAge() {        return age;    }    public void setAge(Integer age) {        this.age = age;    }    /**     * Getter method for property <tt>job</tt>.     *     * @return property value of job     */    public String getJob() {        return job;    }    public void setJob(String job) {        this.job = job;    }}

4. DAO接口

package galaxy.com.db.jdbc;import galaxy.com.db.model.UserDO;public interface UserDAO {    void insert(UserDO userDO);    UserDO queryByUserId(Integer id);}

5. DAO实现类

package galaxy.com.db.jdbc.impl;import galaxy.com.db.jdbc.UserDAO;import galaxy.com.db.model.UserDO;import org.springframework.stereotype.Component;import javax.annotation.Resource;import javax.sql.DataSource;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;@Componentpublic class UserDAOImpl implements UserDAO {    @Resource    private DataSource dataSource;    @Override    public void insert(UserDO userDO) {        String sql = "INSERT INTO user(name, age, job) " +                "VALUES(?,?,?);";        Connection conn = null;        try{            conn = dataSource.getConnection();            PreparedStatement ps = conn.prepareStatement(sql);            ps.setString(1, userDO.getName());            ps.setInt(2, userDO.getAge());            ps.setString(3, userDO.getJob());            ps.executeUpdate();            ps.close();        }catch (Exception e){            e.printStackTrace();        }finally {            if(conn != null){                try {                    conn.close();                } catch (SQLException e) {                }            }        }    }    @Override    public UserDO queryByUserId(Integer id) {        UserDO userDO = null;        String sql = "SELECT * FROM user WHERE id=?";        Connection conn = null;        try{            conn = dataSource.getConnection();            PreparedStatement ps = conn.prepareStatement(sql);            ps.setInt(1, id);            ResultSet resultSet = ps.executeQuery();            if(resultSet.next()){                userDO = new UserDO();                userDO.setId(resultSet.getInt(1));                userDO.setName(resultSet.getString("name"));                userDO.setAge(resultSet.getInt("age"));                userDO.setJob(resultSet.getString("job"));            }            resultSet.close();            ps.close();            return userDO;        }catch (Exception e){            e.printStackTrace();        }finally {            if(conn != null){                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }        return null;    }}

从DAO实现类的代码来看，还是有很多jdbc底层的交互代码需要开发者来实现，不够简洁，接下来会用ibatis来实现db的访问，简化代码，请看下一篇