Java操作数据库的方式

最新推荐文章于 2024-04-20 21:46:52 发布

HEY—key

最新推荐文章于 2024-04-20 21:46:52 发布

阅读量958

点赞数

分类专栏： spring 文章标签： mybatis spring boot

本文链接：https://blog.csdn.net/qq_49044101/article/details/115714965

版权

spring 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

*以下是通过Maven建立的Spring Boot项目为基础进行的操作
文档结构如下：

在这里插入图片描述

1.Java直接操作数据库

1.首先打开数据库创建一个Message的数据库，然后创建一个Student的表

create database Message;
use Message;
create table Student(id int(4) primary key auto_increment not null,name varchar(20),address varchar(20));
insert into Student(id,name,address)values(1,'zhangsan','zhongguo');   #在这里可以继续添加数据

2.然后在pom文件中引入JDBC的依赖

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.23</version> <!--这里可以更改jar包的版本 -->
</dependency>

3.创建Class文件
在这里插入图片描述


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



public class Java_Mysql {
	//8.0以下版本
	
	//static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 
	//这里是jdbc的驱动
	//static final String DB_URL = "jdbc:mysql://localhost:3306/Message";
	//连接你的数据库
	
	
	//8.0以上版本
	static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";  
	static final String DB_URL = "jdbc:mysql://localhost:3306/message?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
	
	
	//需要你的数据库密码和用户名
	static final String UESR= "root";
	static final String PASSWORD = "password";
	
	public static void main(String[] args) throws SQLException {
		Connection con = null;
		Statement st = null;
		try {
			Class.forName(JDBC_DRIVER);
			con = DriverManager.getConnection(DB_URL, UESR, PASSWORD);
			st =  con.createStatement();
			String sql; 
			sql = "select * from student"; //这里是sql语句
			ResultSet rs = st.executeQuery(sql); //查询返回一个结果集
			while(rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String address = rs.getString("address");
				System.out.println(id +"    "+ name +"    "+ address); 
			}
		} catch(SQLException se){
			se.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(!st.isClosed()) {
				st.close();
			}
			if(!con.isClosed()) {
				con.close();
			}
			
		}
	}
}

2.MyBatis操作数据库

首先需要配置mybatis的基础环境，还是继续使用刚才我们建立的student表。首先需要引入依赖，由于我们用spring boot启动项目，所以还需要引入spring的启动器

		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
        </dependency>
        <dependency>
		    <groupId>org.apache.ibatis</groupId>
		    <artifactId>ibatis-sqlmap</artifactId>
		    <version>3.0-beta-10</version>
		</dependency>

建立一个student类，里面的属性要与数据库中的数据一一对应。

public class Student {
	private Integer id;
	private String name;
	private String address;
	@Override
	public String toString() {
		return "id:"+this.id+"    "+"name"+this.name+"    "+"address"+this.address;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	
}

添加一个配置文件application.properties,username和password输入自己的用户名和密码

spring.datasource.url=jdbc:mysql://localhost:3306/message?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=password    
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.initialSize=20
spring.datasource.minIdle=10
spring.datasource.maxActive=100    #properties文件是由键值对的方式存储属性，默认为String的类型，所以不需要加""表明其是字符串，properties文件中不能有空格

创建一个DataSourceConfig类，进行数据源的配置

import javax.sql.DataSource;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.alibaba.druid.pool.DruidDataSource;

@Configuration
public class DataSourceConfig {
	@Bean
	@ConfigurationProperties(prefix = "spring.datasource")
	public DataSource getDruid() {
		return new DruidDataSource();
	}
}

1.使用注解的方式配置Mybatis

共需要二个类和一个映射接口：
在这里我们需要一个StudentMapper接口作为Student表的映射器
还有一个Spring的控制器，和Spring的启动器

StudentMapper：

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

@Mapper
public interface StudentMapper {
	@Select("select * from student where id = #{id}")
	public Student select(Integer id);
	@Insert("insert into student(name,address) values(#{name},#{address})")
	public String insert(String name,String address);
	@Delete("delete from student where id = #{id}")
	public String delete(Integer id);
	@Update("update student set id = #{id},name = #{name},address = #{address} where name = #{origin}")
	public String update(Integer orginId,Integer id ,String name,String address);
}

Boot:

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.SpringApplication;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
@ComponentScan(value = "com.mysql")
@MapperScan(value = "com.mysql")
public class Boot {
	public static void main(String[] args) {
		SpringApplication.run(Boot.class, args);
		
	}
}

Controller:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import com.mysql.mybatis.Student;

@RestController
public class Controller {	
	@Autowired
	private StudentMapper studentmapper;
	@GetMapping("/select")
	public String testSelect() {
		Student stu = studentmapper.select(1);
		System.out.println(stu);
		return "Select Success";
	}
	@GetMapping("/insert")
	public String testInsert() {
		studentmapper.insert("lisi","heilongjiang");
		return "Insert Success";
	}
	@GetMapping("delete")
	public String testDelete() {
		studentmapper.delete(1);
		return "Delete Success";
	}
	@GetMapping("update")
	public String testUpdate() {
		studentmapper.update(1, 3, "wangwu", "xinjiang");
		return "Update Success";
	}
}

2.使用xml文件的方式配置mybatis
新建一个teacher的数据表

use Message;
create table teacher(id int(4) primary key auto_increment not null,name varchar(20),address varchar(20));
insert into teacher(id,name,address)values(1,'zhangsan','zhongguo');

创建一个Teacher类

public class Teacher {
	private int id;
	private String name;
	private String address;
	public  Teacher(int id,String name,String address) {
		this.id = id;
		this.name = name;
		this.address = address;
	}
	@Override
	public String toString() {
		return "id:"+this.id+"    "+"name"+this.name+"    "+"address"+this.address;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
}

创建一个TeacherMapper接口

import org.apache.ibatis.annotations.Mapper;

import com.mysqltest.mybatis.Teacher;

@Mapper
public interface TeacherMapper {
	public Teacher testSelect(Integer id);
	public int testUpdate(Teacher teacher);
}

创建一个TeacherMapper.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>
	<select id="selectTeacher" resultMap="TeacherObject">
		select * from teacher where id = #{id}
	</select>
	<resultMap type="Teacher" id="TeacherObject">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="address" column="address"/>
	</resultMap>
	<update id="updateTeacher" parameterType="Teacher">
		update teacher set name = #{name},address = #{address} where id =#{id}
	</update>
</mapper>

在application.properties文件中加入
mybatis.mapper-location=classpath:mapper/*.xml mybatis.type-aliases-package = com.mysqltest.mybatis
在Controller类中加入

	@Autowired
	private TeacherMapper teachermapper;
	@Test
	public void selectTeacher() {
		Teacher tea = teachermapper.testSelect(1);
		System.out.println(tea);
	}

HEY—key

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
Java操作数据库的方式

*以下是通过Maven建立的Spring Boot项目为基础进行的操作1.Java直接操作数据库首先打开数据库创建一个Message的数据库，然后创建一个Student的表create database Message;use Message;create table Student(id int(4),name varchar(20),address varchar(20));insert into Student(id,name,address)values(1,'zhangsan',
复制链接

扫一扫