About jdbctemplate:
Personally, I feel that it is much more convenient and easier to maintain than the one using Java to link to mysql. You only need to maintain it in the configuration file.
Required packages:
com.springsource.net.sf.cglib-2.2.0.jar
com.springsource.org.aopalliance-1.0.0.jar
com.springsource.org.aspectj.weaver-1.6.8.RELEASE.jar
commons-logging-1.1.3.jar
mysql-connector-java-5.1.7-bin.jar
spring-aop-4.0.0.RELEASE.jar
spring-aspects-4.0.0.RELEASE.jar
spring-beans-4.0.0.RELEASE.jar
spring-context-4.0.0.RELEASE.jar
spring-core-4.0.0.RELEASE.jar
spring-expression-4.0.0.RELEASE.jar
spring-jdbc-4.0.0.RELEASE.jar
spring-orm-4.0.0.RELEASE.jar
spring-tx-4.0.0.RELEASE.jar
spring-web-4.0.0.RELEASE.jar
spring-webmvc-4.0.0.RELEASE.jar
Specific steps:
Configure external resource files (db.properties)
Configure mysal data source
Configure jdbctemplate
bean.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"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
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-4.1.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.1.xsd">
<!-- Package for assembling self-guided scanning -->
<context:component-scan base-package="com.spring.bean.jdbc"></context:component-scan>
<!-- Load external resource files -->
<context:property-placeholder location="classpath:db.properties"/>
<!-- Configure MySQL data source -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${db.driverClassName}"></property>
<property name="url" value="${db.url}"></property>
<property name="username" value="${db.username}"></property>
<property name="password" value="${db.password}"></property>
</bean>
<!--Configure jdbcTemplate template -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource">
<ref bean="dataSource"/>
</property>
</bean>
<!-- Configure namedParameterJdbcTemplate, named parameters -->
<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
<constructor-arg ref="dataSource"></constructor-arg>
</bean>
</beans>
external resource file
db.driverClassName=com.mysql.jdbc.Driver db.url=jdbc:mysql://localhost:3306/students db.username=root db.password=root
Entity students
package com.spring.bean.jdbc;
/**
* Student entity class
* @author Administrator
*
*/
public class Students {
private Integer Id;
private String name;
private String sex;
private int age;
private String tel;
public Integer getId() {
returnId;
}
public void setId(Integer id) {
Id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
@Override
public String toString() {
return "Students [Id=" + Id + ", name=" + name + ", sex=" + sex
+ ", age=" + age + ", tel=" + tel + "]";
}
}
Entity course
package com.spring.bean.jdbc;
public class Course {
private Integer id;
private String coursename;
private String coursenameid;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCoursename() {
return coursename;
}
public void setCoursename(String coursename) {
this.coursename = coursename;
}
public String getCoursenameid() {
return coursenameid;
}
public void setCoursenameid(String coursenameid) {
this.coursenameid = coursenameid;
}
@Override
public String toString() {
return "Course [id=" + id + ", coursename=" + coursename
+ ", coursenameid=" + coursenameid + "]";
}
}
jdbctemplate
package com.spring.bean.jdbc;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
@Repository
public class StudentsDao {
@Autowired
private JdbcTemplate jdbcTemplate;
public Students getStudent(String sql, Integer id) {
RowMapper<Students> rowMapper = new BeanPropertyRowMapper<Students>(Students.class);
Students students = jdbcTemplate.queryForObject(sql, rowMapper, id);
return students;
}
}
jdbcDaosupport
package com.spring.bean.jdbc;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
@Repository
public class CourseDao extends JdbcDaoSupport {
/**You must add dataSource or jdbcTemplate here or the error will be reported as follows
* Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: 'dataSource' or 'jdbcTemplate' is required
* If jdbcTemplate is not added. To use dataSource
* name can only be re-added to the dataSource. Why did I use setDataSource22 to assign a value to the DataSource?
* Because it is modified with the final keyword in the JdbcDaoSupport class, it cannot be rewritten.
* @param dataSource
*/
@Autowired
public void setDataSource22(DataSource dataSource) {
setDataSource(dataSource);
}
/**
* Get courses
* @param sql
* @param id
* @return
*/
public Course getCourse(String sql,int id){
RowMapper<Course> rowMapper = new BeanPropertyRowMapper<Course>(Course.class);
Course course = getJdbcTemplate().queryForObject(sql, rowMapper,id);
return course;
}
}
Please see the notes for test class details.
package com.spring.bean.jdbc;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
public class MainTest {
private ApplicationContext ctx=null;
private StudentsDao studentsDao;
private JdbcTemplate jdbcTemplate;
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private CourseDao courseDao;
{
ctx=new ClassPathXmlApplicationContext("bean.xml");
jdbcTemplate=(JdbcTemplate) ctx.getBean("jdbcTemplate");
studentsDao=(StudentsDao) ctx.getBean("studentsDao");
namedParameterJdbcTemplate=(NamedParameterJdbcTemplate)ctx.getBean("namedParameterJdbcTemplate");
courseDao=(CourseDao) ctx.getBean("courseDao");
}
<br>
/**<br>*Update a certain operation<br> */
@Test
public void getCourse(){
String sql="SELECT * from course WHERE id=?";
Course course = courseDao.getCourse(sql,1);
System.out.println(course);
}
/**
* Use named parameters to operate the database (you can name the fields)
* The advantage is: when there are many fields, there is no need to consider the order of the fields, and the efficiency is high, that is, in the form of key and value
* Disadvantages: A lot of troublesome code
*/
@Test
public void testnamedParameterJdbcTemplate(){
String sql="INSERT INTO students (name, sex, age, tel) VALUES (:n, :s, :a, :t)";
Map<String, Object> paramMap=new HashMap<String,Object >();
paramMap.put("n", "rongrong");
paramMap.put("s", "man");
paramMap.put("a", "25");
paramMap.put("t", "18612396984");
namedParameterJdbcTemplate.update(sql, paramMap);
}
/**
* Use named parameters to operate the database
* Use the update(String sql, SqlParameterSource paramSource) method to pass in entity parameters
* Requirement: The sql field name is consistent with the object attribute name
* SqlParameterSource paramSource
*/
@Test
public void testnamedParameterJdbc(){
String sql="INSERT INTO course (coursename, coursenameid) VALUES (:coursename, :coursenameid);";
Course course = new Course();
course.setCoursenameid("As-1001");
course.setCoursename("java");
SqlParameterSource paramSource = new BeanPropertySqlParameterSource(course);
namedParameterJdbcTemplate.update(sql, paramSource);
}
/**
* Modify a certain attribute of the database
*/
@Test
public void testUpdate(){
String sql="UPDATE students SET `name`=? WHERE id=? ";
jdbcTemplate.update(sql, "lucy",1);
}
/**
* Batch update data
* List<Object[]>here is an array of objects
* Must be an object array, insert a piece of data such as: {"MT-1","man",17,"13706063546"}
* If it is multiple pieces of data, it must be an object array
*/
@Test
public void testBatchUpdate(){
String sql="INSERT INTO students (name, sex, age, tel) VALUES (?, ?, ?, ?)";
List<Object[]> batchArgs=new ArrayList<Object[]>();
batchArgs.add(new Object[]{"MT-1","man",17,"13706063546"});
batchArgs.add(new Object[]{"MT-2","man",18,"13706063547"});
batchArgs.add(new Object[]{"MT-3","man",19,"13706063548"});
batchArgs.add(new Object[]{"MT-4","man",20,"13706063549"});
jdbcTemplate.batchUpdate(sql, batchArgs);
}
/**
* The query returns a collection of entity objects
* Note that the queryForList method is not called
*/
@Test
public void testQueryForList(){
String sql="SELECT id,name, sex, age, tel FROM students";
RowMapper<Students> rowMapper = new BeanPropertyRowMapper<Students>(Students.class);
List<Students> students = jdbcTemplate.query(sql, rowMapper);
// Not a queryForList method
// List<Students> students = jdbcTemplate.queryForList(sql, Students.class);
System.out.println(students);
}
/**
* Get a record from the database and actually get the corresponding object
* Note that the queryForObject(String sql, Class<T> requiredType, Object... args) method is not called!
* You need to call queryForObject(String sql, RowMapper<T> rowMapper, Object... args)
* 1. The RowMapper specifies how to map the rows of the result set. The commonly used implementation class is BeanPropertyRowMapper.
* 2. Use column aliases in SQL to complete the mapping of column names and class attribute names. For example, name, sex, age, tel
* 3. Does not support cascading properties. JdbcTemplate is a JDBC gadget after all, not an ORM framework
*/
@Test
public void testQueryForObject(){
String sql="SELECT id,name, sex, age, tel FROM students WHERE id=?";
RowMapper<Students> rowMapper = new BeanPropertyRowMapper<Students>(Students.class);
Students students = jdbcTemplate.queryForObject(sql, rowMapper,1);
System.out.println(students);
}
/**
* Get the value of a single column, or perform statistical queries
* Use queryForObject(String sql, Class<Long> requiredType)
* Among them: requiredType is Integer.class to achieve the effect of statistical query
* Use int queryForInt(String sql) throws DataAccessException to achieve
*/
@Test
public void testQueryForINt(){
String sql="SELECT count(id) FROM students ";
// RowMapper<Students> rowMapper = new BeanPropertyRowMapper<Students>(Students.class);
int long1 = jdbcTemplate.queryForInt(sql);
// Integer long1 = jdbcTemplate.queryForObject(sql, Integer.class);
// Not a queryForList method
// List<Students> students = jdbcTemplate.queryForList(sql, Students.class);
System.out.println(long1);
}
/**
* Return the user object based on the user id
*/
@Test
public void getStudents(){
String sql="SELECT id,name, sex, age, tel FROM students WHERE id=?";
System.out.println(studentsDao.getStudent(sql, 4));
}
}
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. Java skill treeUsing JDBC to operate databasesJDBC Overview 132,415 people are learning the system