Java 小知识点：JDBC、MyBatis 操作数据库

JDBC 操作 MySQL

• 首先第一步肯定是引入依赖

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
</dependency>

• 接着就是写 Java JDBC 代码了

// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 准备工作
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
// 使用 DriverManager 获取数据库连接
Connection conn = DriverManager.getConnection(url, user, password);
// 创建 Statement 对象，用于发送 SQL
Statement stmt = conn.createStatement();
ResultSet res = stmt.executeQuery("select * from test");
// 操作结果集
while (res.next()) {
    System.out.println(res.getString("test"));
}
// 断开资源
res.close();
stmt.close();
conn.close();

Druid JDBC 操作 MySQL

Druid 是一个数据库连接池，是目前最好的一个数据库连接池吧，在功能、性能、扩展性方面都比其他数据库连接池好（百度看到的）

• 首先第一步也是引入依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.12</version>
</dependency>

• Java 代码操作

// Druid 相关配置
Properties prop = new Properties();
prop.put("driverClassName", "com.mysql.jdbc.Driver");
prop.put("url", "jdbc:mysql://127.0.0.1:3306/test");
prop.put("username", "root");
prop.put("password", "123456");
// 获取连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
// 获取一个连接
Connection connection = dataSource.getConnection();
// 下面就是普通 JDBC 操作了
Statement statement = connection.createStatement();
ResultSet res = statement.executeQuery("select * from test");
while (res.next()) {
    System.out.println(res.getString("test"));
}
res.close();
statement.close();
connection.close();

MyBatis 基于 XML 方式

• 首先也是引入依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.3.1</version>
</dependency>

• Mapper XML 配置编写

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="top.acware.jdbc.XMLMapper">
    <select id="get" resultType="String">
        select * from test
    </select>
</mapper>

• MyBatis Conf XML 配置编写

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="XMLMapper.xml"/>
    </mappers>
</configuration>

• Java 代码编写

public class MyBatisXML {

    public static void main(String[] args) throws Exception{
        // mybatis 配置文件
        InputStream in = Resources.getResourceAsStream("MyBatis Conf XML");
        // 获取一个工厂对象
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(in);
        // 获取 session 会话对象
        SqlSession session = sessionFactory.openSession();
        XMLMapper mapper = session.getMapper(XMLMapper.class);
        for (String res : mapper.get()) {
            System.out.println(res);
        }
        session.close();
        in.close();
    }

}

interface XMLMapper{

    List<String> get();

}

MyBatis 基于注解方式

依赖同 XML 方式

• MyBatis Conf XML 配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
	    <package name="top.acware.jdbc"/>
    </mappers>
</configuration>

• Java 代码编写

public class MyBatisAnnotation {

    public static void main(String[] args) throws Exception{
        // mybatis 配置文件
        InputStream in = Resources.getResourceAsStream("mybatis.xml");
        // 获取一个工厂对象
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(in);
        // 获取 session 会话对象
        SqlSession session = sessionFactory.openSession();
        AnnotationMapper mapper = session.getMapper(AnnotationMapper.class);
        for (String res : mapper.test()) {
            System.out.println(res);
        }
        session.close();
        in.close();
    }

}

interface AnnotationMapper{

    @Select("select * from test")
    List<String> test();

}