概述
在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&characterEncoding=utf-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&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&characterEncoding=utf-8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</bean>
如果数据库版本是mysql5.7,那么需要验证
应用服务器到数据库的网络是否连通,是否有限制
用户名密码以及端口是否正确