JDBC和Mybatis数据库操作,整合springboot

本文展示了如何使用Java进行数据库操作,包括使用JDBC执行SQL插入、查询数据,MyBatis的配置与使用,以及在SpringBoot中实现数据的CRUD操作。内容涵盖mybatis-config.xml配置、UserMapper接口及XML映射文件,以及SpringBoot中Service和Controller的实现。
摘要由CSDN通过智能技术生成

建数据库sql

INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (1, 'zhangsan', '8888888', '张三', 20, 'test1@itcast.cn');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (2, 'lisi', '123456', '李四', 20, 'test2@itcast.cn');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (3, 'wangwu', '123456', '王五', 28, 'test3@itcast.cn');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (4, 'zhaoliu', '123456', '赵六', 21, 'test4@itcast.cn');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (5, 'sunqi', '123456', '孙七', 24, 'test5@itcast.cn');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (6, 'caocao1', '123456', '曹操1', 301, '2@itcast.cn');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (9, 'Philips', '222', '1', NULL, '222@qq.com');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (10, 'name', 'edsfcsaFCsdf', '1', NULL, '111@qq.com');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (13, 'name', 'bjkcvnbjcvbn', 'name', NULL, 'update@qq.com');

User.java

package edu.cs.rg.air.monitor.entity;

import lombok.Data;

@Data
public class User {
    private long     id;
    private String  name;
    private String  email;
    private String  password;

    
    public User() {
    }


    public User(String name, String email, String password) {
        this.name = name;
        this.email = email;
        this.password = password;
    }


    public User(long id, String name, String email, String password) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.password = password;
    }


    @Override
    public String toString(){
        return "User:(id-"+id+" name-"+name+" email-"+email+" password-"+password+" )";
    }
}

项目结构:
在这里插入图片描述

JDBC链接数据库

package edu.cs.rg.air.monitor;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import edu.cs.rg.air.monitor.entity.User;
public class JdbcDemo {
  public static void main(String[] args) throws ClassNotFoundException,SQLException {
    //1.加载驱动
    Class.forName("com.mysql.cj.jdbc.Driver");//固定写法,加载驱动
    //2.用户信息和url
    String url="jdbc:mysql://localhost:3300/test";
    String username = "root";
    String password = "root";
    //3.连接成功,连接数据库对象 Connection
    Connection connection = DriverManager.getConnection(url,username,password);
    //4.执行SQL对象 Statement
    Statement statement = connection.createStatement();
    //5.执行SQL可能存在的结果,查看返回的结果
    String sql = "SELECT * FROM tb_user";
    ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,封装了查询出来的全部结果
    List<User> luser = new ArrayList<User>();
    User nUser = null;
    while(resultSet.next()) {
      nUser = new User();
      nUser.setId((Long)resultSet.getObject("id"));
      nUser.setName((String) resultSet.getObject("NAME"));
      nUser.setEmail((String)resultSet.getObject("email"));
      nUser.setPassword((String)resultSet.getObject("PASSWORD"));
      luser.add(nUser);
      // System.out.println("id="+resultSet.getObject("id"));
      // System.out.println("name="+resultSet.getObject("NAME"));
      // System.out.println("pwd="+resultSet.getObject("PASSWORD"));
      // System.out.println("email="+resultSet.getObject("email"));
    //   System.out.println("birth="+resultSet.getObject("birthday"));
    }
    for(User user : luser){
      System.out.println(user);
    }
    //6.释放连接
    resultSet.close();
    statement.close();
    connection.close();
  }
}

Mybatis 链接数据库

mybatis-config.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="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3300/test"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="mapper/UserMapper.xml"/>
    <!-- <mapper class="edu.cs.rg.air.monitor.mapper.UserMapper"/> -->
  </mappers>
</configuration>

UserMapper.java

package edu.cs.rg.air.monitor.mapper;
import java.util.List;

import edu.cs.rg.air.monitor.entity.User;

public interface UserMapper {
    User selectUser(int id);

    List<User> selectUsers();

    List<User> selectUsersByFuzzy(String name);

    int insertUser(User user);

    int selectCount();

    int deleteUser(long id);

    int updateUser(User user);


}

UserMapper.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="edu.cs.rg.air.monitor.mapper.UserMapper">
  <select id="selectUser" resultType="edu.cs.rg.air.monitor.entity.User">
    select * from tb_user where id = #{id}
  </select>

  <select id="selectCount" resultType="int">
    select COUNT(*)  from tb_user
  </select>

  <select id="selectUsers" resultType="edu.cs.rg.air.monitor.entity.User">
    select * from tb_user
  </select>

  <select id="selectUsersByFuzzy" parameterType="String" resultType="edu.cs.rg.air.monitor.entity.User">
    select * from tb_user
    where name like concat('%',#{name},'%')
  </select>
<!-- (查询多个)
  select * from tb_user
    where 
		name like concat('%',"1",'%') or
		user_name like concat('%',"zhangsan",'%') 
 -->

  <!-- <insert id="insertUser"  >
    insert into tb_user(user_name,password,email) values('Philips','222','222@qq.com');
  </insert> -->

  <insert id="insertUser" parameterType="edu.cs.rg.air.monitor.entity.User">
    insert into tb_user(user_name,password,email) values(#{name},#{password},#{email});
  </insert>


  <delete id="deleteUser">
    delete from tb_user
    where id= #{id}
  </delete>


  <update id="updateUser" parameterType="edu.cs.rg.air.monitor.entity.User">
    update tb_user
    set
    name= #{name},
    password= #{password},
    email= #{email}
    where id = #{id}
  </update>

</mapper>
package edu.cs.rg.air.monitor;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import edu.cs.rg.air.monitor.entity.User;
import edu.cs.rg.air.monitor.mapper.UserMapper;

public class MyBatisDemo {

    public static void main(String[] args){
        String resource = "mybatis-config.xml";
        try (InputStream inputStream = Resources.getResourceAsStream(resource)) {
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            try (SqlSession session = sqlSessionFactory.openSession()) {
                // User user = (User) session.selectOne("edu.cs.rg.monitor.mapper.UserMapper.selectUser", 1);
                UserMapper umapper = session.getMapper(UserMapper.class);
                // User user = umapper.selectUser(1);
                // if(user!=null){
                //     System.out.println("该用户信息2如下:");
                //     System.out.println(user);
                // }else{
                //     System.out.println("没有该用户信息");
                // }
               
                User a = new User(13,"name","update@qq.com","bjkcvnbjcvbn");
                // add User
                // int count = umapper.insertUser(a);

                // select Count from tb_user
                int b = umapper.selectCount();
                // System.out.println(b);
                // int c = umapper.deleteUser(11);
                // System.out.println(c);
                
                int d  = umapper.updateUser(a);


                // List<User> users = umapper.selectUsers();
                // User soutUser = null;
                // for(User soutUser : users){
                //     System.out.println(soutUser.toString());
                // }


                List<User> users = umapper.selectUsersByFuzzy("1");
                for(User soutUser : users){
                    System.out.println(soutUser.toString());
                }

                
                session.commit();
                session.close();
              }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

使用springboot操作数据库

controller——>service——>mapper

在这里插入图片描述

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.5.5</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>edu.cs.rg.air</groupId>
	<artifactId>monitor</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>monitor</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>17</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.29</version>
		</dependency>
		
		<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>

		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.5.9</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

UserController

package edu.cs.rg.air.monitor.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import edu.cs.rg.air.monitor.entity.User;
import edu.cs.rg.air.monitor.service.IUserService;
import edu.cs.rg.air.monitor.service.UserService;

@RestController
@RequestMapping("/user")
public class UserController {
    
    @Autowired
    private IUserService userService;
    
    @GetMapping("/getInfo/{ids}")
    public String queryUserInfo(@PathVariable("ids")Integer id){
        // public String queryUserInfo(Integer id){
        String result = null;
        // id = 1;
        if(id != null && userService != null)
             return userService.selectUser(id).toString();
        else
             return "用户id不存在";
    }



    // @ResponseBody @RequestBody Integer id
    @GetMapping("/getAllInfo")
    public String queryAllUserInfo(){
        String result = null;
        return userService.selectUsers().toString();
        
    }




}

service

package edu.cs.rg.air.monitor.service;

import java.util.List;

import edu.cs.rg.air.monitor.entity.User;

public interface IUserService {
    User selectUser(int id);

    List<User> selectUsers();

    List<User> selectUsersByFuzzy(String name);

    int insertUser(User user);

    int selectCount();

    int deleteUser(long id);

    int updateUser(User user);

}

package edu.cs.rg.air.monitor.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowire;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import edu.cs.rg.air.monitor.entity.User;
import edu.cs.rg.air.monitor.mapper.UserMapper;

@Service
public class UserService implements IUserService {


    @Autowired
    private UserMapper userMapper;


    @Override
    public User selectUser(int id) {
        return userMapper.selectUser(id);
    }

    @Override
    public List<User> selectUsers() {
       
        return userMapper.selectUsers();
    }

    @Override
    public List<User> selectUsersByFuzzy(String name) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public int insertUser(User user) {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public int selectCount() {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public int deleteUser(long id) {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public int updateUser(User user) {
        // TODO Auto-generated method stub
        return 0;
    }
    
}

mapper

package edu.cs.rg.air.monitor.mapper;
import java.util.List;

import edu.cs.rg.air.monitor.entity.User;


public interface UserMapper {
    User selectUser(int id);

    List<User> selectUsers();

    List<User> selectUsersByFuzzy(String name);

    int insertUser(User user);

    int selectCount();

    int deleteUser(long id);

    int updateUser(User user);


}

<?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="edu.cs.rg.air.monitor.mapper.UserMapper">
  <select id="selectUser" resultType="edu.cs.rg.air.monitor.entity.User">
    select * from tb_user where id = #{id}
  </select>

  <select id="selectCount" resultType="int">
    select COUNT(*)  from tb_user
  </select>

  <select id="selectUsers" resultType="edu.cs.rg.air.monitor.entity.User">
    select * from tb_user
  </select>

  <select id="selectUsersByFuzzy" parameterType="String" resultType="edu.cs.rg.air.monitor.entity.User">
    select * from tb_user
    where name like concat('%',#{name},'%')
  </select>
<!-- (查询多个)
  select * from tb_user
    where 
		name like concat('%',"1",'%') or
		user_name like concat('%',"zhangsan",'%') 
 -->

  <!-- <insert id="insertUser"  >
    insert into tb_user(user_name,password,email) values('Philips','222','222@qq.com');
  </insert> -->

  <insert id="insertUser" parameterType="edu.cs.rg.air.monitor.entity.User">
    insert into tb_user(user_name,password,email) values(#{name},#{password},#{email});
  </insert>


  <delete id="deleteUser">
    delete from tb_user
    where id= #{id}
  </delete>


  <update id="updateUser" parameterType="edu.cs.rg.air.monitor.entity.User">
    update tb_user
    set
    name= #{name},
    password= #{password},
    email= #{email}
    where id = #{id}
  </update>

</mapper>

代码下载

以上代码:
https://download.csdn.net/download/m0_61504367/85409418

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

汪程序猿

就当请我吃顿饭

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值