jdbc、jdbc Template 、 mybatis

最新推荐文章于 2025-06-07 10:31:51 发布

陆地火箭

最新推荐文章于 2025-06-07 10:31:51 发布

阅读量503

点赞数

CC 4.0 BY-SA版权

分类专栏： java

本文链接：https://blog.youkuaiyun.com/weixin_39418164/article/details/81874311

java 专栏收录该内容

14 篇文章

订阅专栏

本文详细介绍Java环境下数据库操作方法，从基础的JDBC连接数据库、执行SQL语句到使用JdbcTemplate简化编码，最后介绍MyBatis框架的应用。文章还提供了丰富的示例代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文中代码部分来自：

https://www.cnblogs.com/xiaotiaosi/p/6394554.html

https://www.cnblogs.com/ChenD/p/7080934.html

适合初学者参考。这差不是我开始学习Java的学习过程把，jdbc熬到spring的teplate，后来学了mybatis（初次学感觉orm还需要写sql，我不喜欢写sql，主要是不会，哈哈），后来了解了hibernate和spring data的方便，各有利弊，用到的时候就了解了。

因为整理也花费了一些时间，这里就写原创了，换点积分（Do you think I'm shameless? ahaha）

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


public class javaTest {
   
    public static void main(String[] args) throws ClassNotFoundException, SQLException  {
        String URL="jdbc:mysql://127.0.0.1:3306/imooc?useUnicode=true&amp;characterEncoding=utf-8";
        String USER="root";
        String PASSWORD="tiger";
        //1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2.获得数据库链接
        Connection conn=DriverManager.getConnection(URL, USER, PASSWORD);
        //3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
        Statement st=conn.createStatement();
        ResultSet rs=st.executeQuery("select * from user");
        //4.处理数据库的返回结果(使用ResultSet类)
        while(rs.next()){
            System.out.println(rs.getString("user_name")+" "
                          +rs.getString("user_password"));
        }
        
        //关闭资源
        rs.close();
        st.close();
        conn.close();
    }
}

获取数据库连接：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {

    private static final String URL="jdbc:mysql://127.0.0.1:3306/imooc?useUnicode=true&amp;characterEncoding=utf-8";
    private static final String USER="root";
    private static final String PASSWORD="tiger";
    
    private static Connection conn=null;
    
    static {
        try {
            //1.加载驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            //2.获得数据库的连接
            conn=DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //将获得的数据库与java的链接返回（返回的类型为Connection）
    public static Connection getConnection(){
        return conn;
    }
}

增加表中记录

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


public class javaTest {
   
    public static void main(String[] args) throws ClassNotFoundException, SQLException  {
        String URL="jdbc:mysql://127.0.0.1:3306/imooc?useUnicode=true&amp;characterEncoding=utf-8";
        String USER="root";
        String PASSWORD="tiger";
        //1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2.获得数据库链接
        Connection conn=DriverManager.getConnection(URL, USER, PASSWORD);
        //3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
        String s=""+"insert into user(id,user_name,user_password) values("+"2,?,123)";
        PreparedStatement pst=conn.prepareStatement(s);
        
        pst.setString(1, "xiaoshuai1");
        //pst.setString(2, "123");
            
        pst.execute();        
        //关闭资源        
        pst.close();
        conn.close();
    }
}

调用无参存储过程

存储过程：
 CREATE  PROCEDURE SP_select_nofilter3() 
 BEGIN
      SELECT * FROM file;
 END;

代码：
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


public class javaTest {
   
    public static void main(String[] args) throws ClassNotFoundException, SQLException  {
        String URL="jdbc:mysql://127.0.0.1:3306/imooc?useUnicode=true&amp;characterEncoding=utf-8";
        String USER="root";
        String PASSWORD="tiger";
        //1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2.获得数据库链接
        Connection conn=DriverManager.getConnection(URL, USER, PASSWORD);
        //3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
        String s="call SP_select_nofilter3() ";
        CallableStatement cst=conn.prepareCall(s);
            
        ResultSet rs=cst.executeQuery();
        
        while(rs.next()){
            System.out.println(rs.getString("user_name"));
        }
        //关闭资源        
        cst.close();
        conn.close();
    }
}

调用IN参数存储过程

存储过程
CREATE  PROCEDURE SP_select_nofilter4(IN SP_name varchar(20)) 
BEGIN
IF SP_name=null OR SP_name=' ' THEN
     SELECT * FROM file;
ELSE
    SELECT * FROM file WHERE user_name=SP_name;
END IF;
END;

代码
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


public class javaTest {
   
    public static void main(String[] args) throws ClassNotFoundException, SQLException  {
        String URL="jdbc:mysql://127.0.0.1:3306/imooc?useUnicode=true&amp;characterEncoding=utf-8";
        String USER="root";
        String PASSWORD="tiger";
        //1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2.获得数据库链接
        Connection conn=DriverManager.getConnection(URL, USER, PASSWORD);
        //3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
        /*查询不带参数*/
        String s="call SP_select_nofilter4(?) ";
        CallableStatement cst=conn.prepareCall(s);
        cst.setString(1," ");    
        ResultSet rs=cst.executeQuery();
        
        while(rs.next()){
            System.out.println(rs.getString("id")+"  "+rs.getString("user_name"));
        }
        System.out.println("*******************************");
        //查询带参数
        String s1="call SP_select_nofilter4(?)";
        CallableStatement cst1=conn.prepareCall(s1);
        cst1.setString(1, "xiao1");
        ResultSet rs1=cst1.executeQuery();

        while(rs1.next()){
            System.out.println(rs1.getString("id")+"  "+rs1.getString("user_name"));
        }
        
        //关闭资源    
        rs1.close();
        cst1.close();
        rs.close();
        cst.close();
        
        conn.close();
    }
}

使用jdbc template

package com.spring.test;

import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class TestJDBCTemplate {

    @Test
    public void test1() {

        // JDBC模板依赖于连接池来获得数据的连接，所以必须先要构造连接池
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/spring");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");

        // 创建JDBC模板
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        // 这里也可以使用构造方法
        jdbcTemplate.setDataSource(dataSource);

        // sql语句
        String sql = "select count(*)  from user";
        Long num = (long) jdbcTemplate.queryForObject(sql, Long.class);

        System.out.println(num);

    }

}

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd
    http://www.springframework.org/schema/aop
    http://www.springframework.org/schema/aop/spring-aop.xsd
    http://www.springframework.org/schema/tx
    http://www.springframework.org/schema/tx/spring-tx.xsd">

    <!-- IOC和DI的注解扫描 -->
    <context:component-scan base-package="com.spring" ></context:component-scan>

    <!-- 打开AOP的注解 -->
    <!-- 这里用的是中间的横线而不是下划线 -->
    <aop:aspectj-autoproxy></aop:aspectj-autoproxy>

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/spring_03"></property>
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="user" value="root"></property>
        <property name="password" value="123456"></property>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <bean id="userDao" class="com.spring.dao.UserDao">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

    <bean id="userService" class="com.spring.service.UserService">
        <property name="userDao" ref="userDao"></property>
    </bean>

</beans>



package com.spring.dao;

import org.springframework.jdbc.core.JdbcTemplate;

import com.spring.domain.User;

public class UserDao {

    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void addUser(User user) {

        String sql = "insert into user (username, password) values (?, ?)";

        jdbcTemplate.update(sql, user.getUsername(), user.getPassword());

    }

}

package com.spring.service;

import com.spring.dao.UserDao;
import com.spring.domain.User;

public class UserService {

    // 加入userDao作为成员变变量
    private UserDao userDao;

    // 注意这里要增加get和set方法
    public UserDao getUserDao() {
        return userDao;
    }

    public void setUserDao(UserDao userDao) {
        this.userDao = userDao;
    }

    public void addUser(User user) {
        userDao.addUser(user);
    }
}

package com.spring.test;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.spring.domain.User;
import com.spring.service.UserService;

public class TestJDBCTemplate {

    @Test
    public void test2() {

        ApplicationContext ctx = new ClassPathXmlApplicationContext("bean.xml");
        UserService userService = (UserService) ctx.getBean("userService");

        User user = new User();
        user.setPassword("111");
        user.setUsername("小王");

        userService.addUser(user);

    }

}

mybatis

1、 mybatis配置

SqlMapConfig.xml，此文件作为mybatis的全局配置文件，配置了mybatis的运行环境等信息。

mapper.xml文件即sql映射文件，文件中配置了操作数据库的sql语句。此文件需要在SqlMapConfig.xml中加载。

2、通过mybatis环境等配置信息构造SqlSessionFactory即会话工厂

3、由会话工厂创建sqlSession即会话，操作数据库需要通过sqlSession进行。

4、 mybatis底层自定义了Executor执行器接口操作数据库，Executor接口有两个实现，一个是基本执行器、一个是缓存执行器。

5、 Mapped Statement也是mybatis一个底层封装对象，它包装了mybatis配置信息及sql映射信息等。mapper.xml文件中一个sql对应一个Mapped Statement对象，sql的id即是Mapped statement的id。

6、 Mapped Statement对sql执行输入参数进行定义，包括HashMap、基本类型、pojo，Executor通过 Mapped Statement在执行sql前将输入的java对象映射至sql中，输入参数映射就是jdbc编程中对preparedStatement设置参数。

7、 Mapped Statement对sql执行输出结果进行定义，包括HashMap、基本类型、pojo，Executor通过 Mapped Statement在执行sql后将输出结果映射至java对象中，输出结果映射过程相当于jdbc编程中对结果的解析处理过程。

<?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="environment">
        <environment id="environment">
            <transactionManager type="JDBC" />
            <!-- mybatis 自带连接池 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/medicine?useUnicode=true&amp;characterEncoding=utf-8" />
                <property name="username" value="root" />
                <property name="password" value="root" />
            </dataSource>
        </environment>
    </environments>
    <!-- 定义sqlMapper文件位置的 -->
    <mappers>
        <mapper resource="com/mxp/mybatis/entity/UserMapper.xml" />
    </mappers> 
</configuration>

package com.mxp.mybatis.entity;

import java.io.Serializable;

public class User implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    private String id;
    private String userName;
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    
}

<?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="com.mxp.mybatis.entity.UserMapper">
   
    <sql id="userColumns">
        a.id AS "id",
        a.user_name AS "userName"
    </sql>
    
    <select id="findAll" resultType="com.mxp.mybatis.entity.User" >
            SELECT <include refid="userColumns"/>
                FROM test_user a
    </select>
    
    <select id="findLikeName" parameterType="string" resultType="com.mxp.mybatis.entity.User" >
        SELECT <include refid="userColumns"/>
                FROM test_user a
                <where>
                    a.user_name like #{name}
                </where>
                 
    </select>
    
</mapper>

package com.mxp.mybatis.util;

import java.io.InputStream;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MybatisUtil {
    
    public static SqlSession getInsertance(){
        SqlSessionFactoryBuilder builder = 
                new SqlSessionFactoryBuilder();
        //这是读取文件后 形成一个输入流 涨知识了
        //Test 其实就是一个类型 写自己的什么类型都行 主要是为了获取到getClassLoader().getResourceAsStream
        InputStream reader = MybatisUtil.class.getClassLoader().getResourceAsStream("SqlMapConfig.xml");
        //获取sqlsessionFactory
        SqlSessionFactory factory =  builder.build(reader);
        //获取session
        SqlSession session = factory.openSession();
        return session;
    }

}

package test;

import java.io.IOException;

import java.util.List;




import org.apache.ibatis.session.SqlSession;
import com.mxp.mybatis.util.MybatisUtil;

public class Test {
  
    @org.junit.Test
    public void getSqllSession() throws IOException{
        
        SqlSession session = MybatisUtil.getInsertance();
        System.out.println("获取session");
        session.close();
    }
    @org.junit.Test
    public void findall(){
        SqlSession sqlsession = MybatisUtil.getInsertance();
        List<com.mxp.mybatis.entity.User> list =  sqlsession.selectList("findAll");
        for(com.mxp.mybatis.entity.User u:list){
            System.out.println(u.getUserName());
        }
        sqlsession.close();
        
        
    }
    @org.junit.Test
    public void findLike(){
        SqlSession sqlsession = MybatisUtil.getInsertance();
        List<com.mxp.mybatis.entity.User> list =  sqlsession.selectList("findLikeName","%文%");
        for(com.mxp.mybatis.entity.User u:list){
            System.out.println(u.getUserName());
        }
        sqlsession.close();
        
        
    }
}