Spring JdbcTemplate-白红宇

Spring JdbcTemplate

阅读量：4050 次

发布时间：2019-05-25

本文共 10625 字，大约阅读时间需要 35 分钟。

– Start

package shangbo.spring.jdbc.example1;import org.springframework.context.ApplicationContext;import org.springframework.context.annotation.AnnotationConfigApplicationContext;public class App {	public static void main(String[] args) throws Exception {		// 实例化 Spring IoC 容器		ApplicationContext context = new AnnotationConfigApplicationContext(AppConfig.class);		// 从容器中获得 BusinessService 的实例		BusinessService service = context.getBean(BusinessService.class);		// 业务逻辑		service.doWork();	}}

package shangbo.spring.jdbc.example1;import javax.sql.DataSource;import org.apache.commons.dbcp.BasicDataSource;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.jdbc.datasource.DataSourceTransactionManager;import org.springframework.transaction.annotation.EnableTransactionManagement;@Configuration@EnableTransactionManagement // 开启事务管理public class AppConfig {	@Bean(destroyMethod="close")	public BasicDataSource dataSource() {		BasicDataSource dataSource = new BasicDataSource();		dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");		dataSource.setUrl("jdbc:oracle:thin:@localhost:1521:xe");		dataSource.setUsername("hr");		dataSource.setPassword("123456");		return dataSource;	}	@Bean	public DataSourceTransactionManager txManager(DataSource dataSource) {		DataSourceTransactionManager txManager = new DataSourceTransactionManager();		txManager.setDataSource(dataSource);		return txManager;	}	@Bean	public JobDao jobDao(DataSource dataSource) {		JobDao jobDao = new JobDaoImpl();		jobDao.setDataSource(dataSource);		return jobDao;	}	@Bean	public BusinessService businessService(JobDao jobDao) {		BusinessService businessService = new BusinessServiceImpl();		businessService.setJobDao(jobDao);		return businessService;	}}

package shangbo.spring.jdbc.example1;public interface BusinessService {	void doWork();	void setJobDao(JobDao jobDao);}

package shangbo.spring.jdbc.example1;import org.springframework.transaction.annotation.Transactional;public class BusinessServiceImpl implements BusinessService {	private JobDao jobDao;	@Transactional	public void doWork() {		// Call Function		// System.out.println(jobDao.queryJobTitleById("IT"));		// System.out.println(jobDao.queryJob("IT"));		// Call Function		// System.out.println(jobDao.queryJobTitleById2("IT"));		System.out.println(jobDao.queryJob2("IT"));	}	public void setJobDao(JobDao jobDao) {		this.jobDao = jobDao;	}}

package shangbo.spring.jdbc.example1;import java.util.List;import javax.sql.DataSource;public interface JobDao {	//	// DDL	//	void createTableJobs();	//	// Insert	//	int insertJob(Job job);	int[] insertJob(List
   
     jobs);	//	// Update	//	int updateJob(Job job);	//	// Query	//	Job queryJobById(String jobId);	List
    
      queryAllJob();	//	// Delete	//	int deleteJobById(String jobId);	//	// Call Stored Procedure	//	String queryJobTitleById2(String jobId);	Job queryJob2(String jobId);	//	// Call Function	//	String queryJobTitleById(String jobId);	Job queryJob(String jobId);	//	// Setter	//	void setDataSource(DataSource dataSource);}

package shangbo.spring.jdbc.example1;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.sql.Types;import java.util.ArrayList;import java.util.List;import java.util.Map;import javax.sql.DataSource;import org.springframework.jdbc.core.BatchPreparedStatementSetter;import org.springframework.jdbc.core.BeanPropertyRowMapper;import org.springframework.jdbc.core.CallableStatementCreator;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;import org.springframework.jdbc.core.PreparedStatementCreator;import org.springframework.jdbc.core.SqlOutParameter;import org.springframework.jdbc.core.SqlParameter;import org.springframework.jdbc.support.GeneratedKeyHolder;import org.springframework.jdbc.support.KeyHolder;import org.springframework.stereotype.Repository;import oracle.jdbc.OracleTypes;@Repositorypublic class JobDaoImpl implements JobDao {	private JdbcTemplate jdbcTemplate;	//	// DDL	//	public void createTableJobs() {		String sql = "create table jobs (job_id varchar2(10), job_title varchar2(35), min_salary number(6,0), max_salary number(6,0))";		jdbcTemplate.execute(sql);	}	//	// Insert	//	public int insertJob(Job job) {		String sql = "insert into jobs values (?, ?, ?, ?)";		return jdbcTemplate.update(sql, job.getJobId(), job.getJobTitle(), job.getMinSalary(), job.getMaxSalary());	}	public int insertJob2(final Job job) {		final String sql = "insert into jobs values (job_seq.nextval, ?, ?, ?)";		KeyHolder keyHolder = new GeneratedKeyHolder(); // 返回生成的 key		jdbcTemplate.update(new PreparedStatementCreator() {			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {				PreparedStatement ps = connection.prepareStatement(sql, new String[] { "job_id" });				ps.setString(1, job.getJobTitle());				ps.setInt(2, job.getMinSalary());				ps.setInt(3, job.getMaxSalary());				return ps;			}		}, keyHolder);		return jdbcTemplate.update(sql, job.getJobId(), job.getJobTitle(), job.getMinSalary(), job.getMaxSalary());	}	public int[] insertJob(final List
   
     jobs) {		String sql = "insert into jobs values (?, ?, ?, ?)";		// 批量插入		return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {			public void setValues(PreparedStatement ps, int i) throws SQLException {				ps.setString(1, jobs.get(i).getJobId());				ps.setString(2, jobs.get(i).getJobTitle());				ps.setInt(3, jobs.get(i).getMinSalary());				ps.setInt(4, jobs.get(i).getMaxSalary());			}			public int getBatchSize() {				return jobs.size();			}		});	}	public int[][] insertJob2(final List
    
      jobs) {		String sql = "insert into jobs values (?, ?, ?, ?)";		// 批量插入,每批次 100		return jdbcTemplate.batchUpdate(sql, jobs, 100, new ParameterizedPreparedStatementSetter
     
      () {			public void setValues(PreparedStatement ps, Job argument) throws SQLException {				ps.setString(1, argument.getJobId());				ps.setString(2, argument.getJobTitle());				ps.setInt(3, argument.getMinSalary());				ps.setInt(4, argument.getMaxSalary());			}		});	}	//	// Update	//	public int updateJob(Job job) {		String sql = "update jobs set job_title=?, min_salary=?, max_salary=? where job_id = ?";		return jdbcTemplate.update(sql, job.getJobTitle(), job.getMinSalary(), job.getMaxSalary(), job.getJobId());	}	//	// Query	//	public Job queryJobById(String jobId) {		String sql = "select * from jobs where job_id = ?";		return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper
      
       (Job.class), jobId);	}	public List
       
         queryAllJob() {		String sql = "select * from jobs";		return jdbcTemplate.query(sql, new BeanPropertyRowMapper
        
         (Job.class)); } // // Delete // public int deleteJobById(String jobId) { String sql = "delete from jobs where job_id = ?)"; return jdbcTemplate.update(sql, jobId); } // // Call Stored Procedure // public String queryJobTitleById2(final String jobId) { List
         
           declaredParameters = new ArrayList
          
           (); declaredParameters.add(new SqlParameter("job_id", Types.VARCHAR)); declaredParameters.add(new SqlOutParameter("job_title", Types.VARCHAR)); // Map
           
             r = jdbcTemplate.call(new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) throws SQLException { String sql = "{call query_job_title_by_id2(?,?)}"; CallableStatement statement = con.prepareCall(sql); statement.setString(1, jobId); statement.registerOutParameter(2, Types.VARCHAR); return statement; } }, declaredParameters); return r.get("job_title").toString(); } public Job queryJob2(final String jobId) { List
            
              declaredParameters = new ArrayList
             
              (); declaredParameters.add(new SqlParameter("job_id", Types.VARCHAR)); declaredParameters.add(new SqlOutParameter("job", OracleTypes.CURSOR, new BeanPropertyRowMapper
              
               (Job.class))); // Map
               
                 r = jdbcTemplate.call(new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) throws SQLException { String sql = "{call query_job2(?,?)}"; CallableStatement statement = con.prepareCall(sql); statement.setString(1, jobId); statement.registerOutParameter(2, OracleTypes.CURSOR); return statement; } }, declaredParameters); return ((List
                
                 ) r.get("job")).get(0); } // // Call Function // public String queryJobTitleById(final String jobId) { List
                 
                   declaredParameters = new ArrayList
                  
                   (); declaredParameters.add(new SqlOutParameter("job_title", Types.VARCHAR)); // Map
                   
                     r = jdbcTemplate.call(new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) throws SQLException { String sql = "{call ? = query_job_title_by_id(?)}"; CallableStatement statement = con.prepareCall(sql); statement.registerOutParameter(1, Types.VARCHAR); statement.setString(2, jobId); return statement; } }, declaredParameters); return r.get("job_title").toString(); } public Job queryJob(final String jobId) { List
                    
                      declaredParameters = new ArrayList
                     
                      (); declaredParameters.add(new SqlOutParameter("job", OracleTypes.CURSOR, new BeanPropertyRowMapper
                      
                       (Job.class))); // Map
                       
                         r = jdbcTemplate.call(new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) throws SQLException { String sql = "{call ? = query_job(?)}"; CallableStatement statement = con.prepareCall(sql); statement.registerOutParameter(1, OracleTypes.CURSOR); statement.setString(2, jobId); return statement; } }, declaredParameters); return ((List
                        
                         ) r.get("job")).get(0); } // // Setter // public void setDataSource(DataSource dataSource) { jdbcTemplate = new JdbcTemplate(dataSource); }}

package shangbo.spring.jdbc.example1;public class Job {	private String jobId;	private String jobTitle;	private Integer minSalary;	private Integer maxSalary;	public String getJobId() {		return jobId;	}	public void setJobId(String jobId) {		this.jobId = jobId;	}	public String getJobTitle() {		return jobTitle;	}	public void setJobTitle(String jobTitle) {		this.jobTitle = jobTitle;	}	public Integer getMinSalary() {		return minSalary;	}	public void setMinSalary(Integer minSalary) {		this.minSalary = minSalary;	}	public Integer getMaxSalary() {		return maxSalary;	}	public void setMaxSalary(Integer maxSalary) {		this.maxSalary = maxSalary;	}	public String toString() {		return "Job[jobId=" + jobId + ", jobTitle=" + jobTitle + ", minSalary=" + minSalary + ", maxSalary=" + maxSalary + "]";	}}

–

– 声明：转载请注明出处

– Last Updated on 2017-06-17

– Written by ShangBo on 2017-06-17

– End

你可能感兴趣的文章