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;

@Component
public 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的访问,简化代码,请看下一篇



©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页