spring jdbc 配置数据源连接数据库

 

概述

在XML配置数据源，并将数据源注册到JDBC模板

JDBC模板关联业务增删改查

 

在XML配置数据源

<?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://192.168.223.129:3358/vodb?useUnicode=true&amp;characterEncoding=utf-8&amp;autoReconnect=true&amp;useSSL=false&amp;zeroDateTimeBehavior=convertToNull&amp;serverTimezone=Asia/Shanghai"/>
       <property name="username" value="automng"/>
       <property name="password" value="Automng_123"/>
    </bean>
    <bean id="studentJDBCTemplate" 
      class="data.model.StudentJDBCTemplate">
      <property name="dataSource"  ref="dataSource" />    
   </bean>

</beans>

 

JDBC模板关联业务增删改查

package data.model;

import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

public class StudentJDBCTemplate implements StudentDAO {

       private DataSource dataSource;
       private JdbcTemplate jdbcTemplateObject; 

       
       public void setDataSource(DataSource dataSource) {
          this.dataSource = dataSource;
          this.jdbcTemplateObject = new JdbcTemplate(dataSource);
       }

      
       public void create(String name, Integer age) {
          String SQL = "insert into Student (name, age) values (?, ?)";     
          jdbcTemplateObject.update( SQL, name, age);
          System.out.println("Created Record Name = " + name + " Age = " + age);
          return;
       }

       
       public Student getStudent(Integer id) {
          String SQL = "select * from Student where id = ?";
          Student student = jdbcTemplateObject.queryForObject(SQL, 
                            new Object[]{id}, new StudentMapper());
          return student;
       }

       
       public List<Student> listStudents() {
          String SQL = "select * from Student";
          List <Student> students = jdbcTemplateObject.query(SQL, 
                                    new StudentMapper());
          return students;
       }

       
       public void delete(Integer id){
          String SQL = "delete from Student where id = ?";
          jdbcTemplateObject.update(SQL, id);
          System.out.println("Deleted Record with ID = " + id );
          return;
       }

       
       public void update(Integer id, Integer age){
          String SQL = "update Student set age = ? where id = ?";
          jdbcTemplateObject.update(SQL, age, id);
          System.out.println("Updated Record with ID = " + id );
          return;
       }

    }

 

 

 

遇到问题

Client does not support authentication protocol requested by server; consider upgrading MySQL client

最新的mysql模块并未完全支持MySQL 8的“caching_sha2_password”加密方式，而“caching_sha2_password”在MySQL 8中是默认的加密方式。因此，下面的方式命令是默认已经使用了“caching_sha2_password”加密方式，该账号、密码无法在mysql模块中使用。

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

<bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
       <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
       <property name="url" value="jdbc:mysql://127.0.0.1:3306/vodb?useUnicode=true&amp;characterEncoding=utf-8"/>
       <property name="username" value="root"/>
       <property name="password" value="123456"/>
    </bean>

 

如果数据库版本是mysql5.7，那么需要验证

应用服务器到数据库的网络是否连通，是否有限制

用户名密码以及端口是否正确