JdbcTemplate连接数据库常用的三种方式

最新推荐文章于 2024-09-11 11:48:38 发布

@松

最新推荐文章于 2024-09-11 11:48:38 发布

阅读量3.3k

点赞数

分类专栏： jdbctemplate springframework database 文章标签： springframework jdbctemplate java

本文链接：https://blog.csdn.net/weixin_45285459/article/details/100689329

版权

database 同时被 3 个专栏收录

3 篇文章 0 订阅

订阅专栏

jdbctemplate

1 篇文章 0 订阅

订阅专栏

springframework

1 篇文章 0 订阅

订阅专栏

1.spring 内置数据源
导入架包:
mysql-connector-java.jar
spring-beans.jar
spring-core.jar
spring-jdbc.jar
spring-tx.jar
spring-test.jar

测试文件

/**
 * 
 */
package com.igeek.test;


import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;


/**
 * @author Administrator
 *
 */
public class JdbcTemplateTest {

	@Test
	public void test()
	{
		//目标:使用jdbctemplate 执行sql
		//1. 构建数据库
		//spring 内置了一个数据库
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql:///igeekspring?serverTimezone=UTC");
		dataSource.setUsername("root");
		dataSource.setPassword("123456");
		
		//2. 创建jdbctemplate 实例
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		//3. 执行sql,创建表test001
		jdbcTemplate.execute("create table if not exists test001(id int,name varchar(20))");
	}

	
}

清单文件:

<?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:aop="http://www.springframework.org/schema/aop"
     xsi:schemaLocation="
      http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        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.xsd"> <!-- bean definitions here -->
 <context:component-scan base-package="com.igeek"></context:component-scan> 
 
	<!-- 配置内置的数据源 bean -->
	 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
		<property name="url" value="jdbc:mysql:///igeekspring?serverTimezone=UTC"></property>
		<property name="username" value="root"></property>
		<property name="password" value="123456"></property>
	</bean>
	
	<!-- jdbctemplate 对象 -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
</beans>

2. Apache dbcp 配置
com.springsource.org.aopalliance.jar
com.springsource.org.aspectj.weaver.jar
mysql-connector-java.jar
spring-aop.jar
spring-aspects.jar
spring-beans.jar
spring-context.jar
spring-core.jar
spring-expression.jar
spring-jdbc.jar
spring-tx.jar
spring-test.jar

com.springsource.org.apache.commons.dbcp.jar
com.springsource.org.apache.commons.pool.jar

清单配置文件

<?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:aop="http://www.springframework.org/schema/aop"
     xsi:schemaLocation="
      http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        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.xsd"> <!-- bean definitions here -->
 <context:component-scan base-package="com.igeek"></context:component-scan> 
 
	<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
		<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
		<property name="url" value="jdbc:mysql:///igeekspring?serverTimezone=UTC"></property>
		<property name="username" value="root"></property>
		<property name="password" value="123456"></property>
	</bean> 
	
	
	<!-- jdbctemplate 对象 -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
</beans>

测试文件 JdbcTempleTest

/**
 * 
 */
package com.igeek.test;


import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class JdbcTemplateTest {


	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Test
	public void test1()
	{
		jdbcTemplate.execute("create table if not exists test002(id int,name varchar(20))");
	}
	
	
}

3. C3P0 连接配置
com.springsource.com.mchage.v2.c3p0.jar

com.springsource.org.aopalliance.jar
com.springsource.org.aspectj.weaver.jar
mysql-connector-java.jar
spring-aop.jar
spring-aspects.jar
spring-beans.jar
spring-context.jar
spring-core.jar
spring-expression.jar
spring-jdbc.jar
spring-tx.jar
spring-test.jar

清单配置文件

<?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:aop="http://www.springframework.org/schema/aop"
     xsi:schemaLocation="
      http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        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.xsd"> <!-- bean definitions here -->
 <context:component-scan base-package="com.igeek"></context:component-scan> 
	
	 <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property>
		<property name="jdbcUrl" value="jdbc:mysql:///igeekspring?serverTimezone=UTC"></property>
		<property name="user" value="root"></property>
		<property name="password" value="123456"></property>
	</bean> 
	
	<!-- jdbctemplate 对象 -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
</beans>

测试文件 jdbctemplatetest

/**
 * 
 */
package com.igeek.test;


import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;


/**
 * @author Administrator
 *
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class JdbcTemplateTest {

	

	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Test
	public void test2()
	{
		jdbcTemplate.execute("create table if not exists test004(id int,name varchar(20))");
	}
	
}

简单操作
应用层对象文件
BookDao.java

/**
 * 
 */
package com.igeek.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.igeek.domain.Book;

/**
 * @author Administrator
 *
 */
@Repository
public class BookDao extends JdbcDaoSupport{
	
	
	
	//保存一本书
	public void save()
	{
		String sql = "insert into book (book.id,book.name,book.price)values(null,?,?)";
		//pst.setString(1,"浪潮之巅");
		//pst.setPrice(23.0);
		Book book =  new Book();
		book.setName("浪潮之巅");//吴军
		book.setPrice(23.0);
		this.getJdbcTemplate().update(sql,book.getName(),book.getPrice());
	
	}
	
	public void update()
	{
		String sql = "update book set book.name = ? where id = ?";
		this.getJdbcTemplate().update(sql, "java",1);
	}
	
	public void delete()
	{
		String sql = "delete frome book where id = ?";
		this.getJdbcTemplate().update(sql, 1);
	}
	
	
	public void queryBook() throws SQLException
	{
		PreparedStatement statement = null;
		ResultSet rs = statement.executeQuery();
		while(rs.next())
		{
			int id = rs.getInt(1);
			String name = rs.getString(2);
			double price = rs.getDouble(3);
			
			Book book = new Book();
			book.setId(id);
			book.setName(name);
			book.setPrice(price);
		}
	}
	
	
	public Book findById(int id)
	{
		String sql = "select * from book where id = ? ";
		return this.getJdbcTemplate().queryForObject(sql, new BookRowMapper(),id);
	}
	
	public List<Book> findAll()
	{
		String sql = "select * from book";
		return this.getJdbcTemplate().query(sql, new BookRowMapper());
	}
	
	public List<Book> findCondition(String name)
	{
		String sql = "select * from book where book.name like ?";
		
		return this.getJdbcTemplate().query(sql, new BookRowMapper(),"%"+ name + "%");
	}
	
	
	
	
	class BookRowMapper implements RowMapper<Book>{

		@Override
		public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
			// TODO Auto-generated method stub
			Book book = new Book();
			book.setId(rs.getInt(1));
			book.setName(rs.getString(2));
			book.setPrice(rs.getDouble(3));
			return book;
		}
		
	}
}

数据 Book.java

/**
 * 
 */
package com.igeek.domain;

/**
 * @author Administrator
 *
 */
public class Book {

	private int id;
	private double price;
	private String name;
	public void setId(int id) {
		this.id = id;
	}
	public void setPrice(double price) {
		this.price = price;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getId() {
		return id;
	}
	public double getPrice() {
		return price;
	}
	public String getName() {
		return name;
	}
	@Override
	public String toString() {
		return "Book [id=" + id + ", price=" + price + ", name=" + name + "]";
	}
	
	
}

测试:

/**
 * 
 */
package com.igeek.test;

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.igeek.dao.BookDao;
import com.igeek.domain.Book;

/**
 * @author Administrator
 *
 */

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class DaoTest {

	
	@Autowired
	private BookDao bookDao;
	
	@Test
	public void test()
	{
		bookDao.save();
	}
	
	@Test
	public void testQuery()
	{
		Book b = bookDao.findById(1);
		System.out.println(b);
	}
	
	@Test
	public void testQueryAll()
	{
		List<Book> bookList = bookDao.findAll();
		for(Book book: bookList)
		{
			System.out.println(book);
		}
	}
	
	/***
	 * 设置编码格式:
	 *show varibales like '%char%';
	 *set  character_set_server= utf8;
	 *set character_set_database = utf8;
	 */
	@Test
	public void testQueryCondition()
	{
		//
		List<Book> books = bookDao.findCondition("西游");
		for(Book book : books)
		{
			System.out.println(book);
		}
	}
	
}

清单配置文件 applicationContext.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:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
     xsi:schemaLocation="
      http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        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.xsd">
         <!-- bean definitions here -->
         
 	<context:component-scan base-package="com.igeek"></context:component-scan> 
 
	<!-- 配置内置的数据源 bean -->
	
 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
		<property name="url" value="jdbc:mysql:///igeekspring?serverTimezone=UTC"></property>
		<property name="username" value="root"></property>
		<property name="password" value="123456"></property>
	</bean>
	
	<bean id="bookDao" class= "com.igeek.dao.BookDao">
	<property name="dataSource" ref="dataSource"></property>
	</bean>
	
</beans>