Spring Data_学习记录_05

1. Named Parameter JDBC Template

• org.zcs.springjdbc-JdbcDemo.java

package org.zcs.springjdbc;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.zcs.springjdbc.dao.JdbcDaoImpl;
import org.zcs.springjdbc.model.Circle;

public class JdbcDemo {
	public static void main(String[] args) {
		ApplicationContext ctx = new ClassPathXmlApplicationContext("spring.xml");
		JdbcDaoImpl dao = ctx.getBean("jdbcDaoImpl", JdbcDaoImpl.class);
		//Circle circle = dao.getCircle(1);
		//System.out.println(circle.getName());
		//System.out.println(dao.getCircleCount());
		//System.out.println(dao.getCircleforId(1));
		
		//System.out.println(dao.getAllCircles() );
		
		//dao.insertCircle(new Circle(4, "Forth Circle "));
		
		//System.out.println(dao.getAllCircles() );
		//dao.insertCircle(new Circle(5, "Five Circle "));
		System.out.println(dao.getAllCircles() );
		//dao.createTriangleTable();
	}
}

• org.zcs.springjdbc.dao-JdbcDaoImpl.java

package org.zcs.springjdbc.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;
import org.zcs.springjdbc.model.Circle;

import com.mysql.jdbc.Statement;
@Component
public class JdbcDaoImpl {	

	private DataSource dataSource;
	private JdbcTemplate jdbcTemplate = new JdbcTemplate();
	
	 private NamedParameterJdbcTemplate  namedParameterJdbcTemplate;
	public Circle getCircle(int circleId) {
	
			
	        try {
 
	        Connection con = (Connection) dataSource.getConnection(); //连接数据库
 
	        String sql = "Select * from springdata where id = ?";//对表的操作语句
	        PreparedStatement ps = con.prepareStatement(sql);
	        ps.setInt(1, circleId);
	        Circle circle = null;
	        ResultSet rs = ps.executeQuery();
	        if(rs.next()) {
	        	circle = new Circle(circleId, rs.getString("name"));
	        }
	       
	        rs.close();
	        
	        ps.close();
	        con.close();
	        return circle;
	        
	        }catch (Exception e) {
	        	throw new RuntimeException(e);
	        } 
	}

	 
	public int getCircleCount() {
		String sql = "SELECT COUNT(*) FROM springdata";
		jdbcTemplate.setDataSource(getDataSource());
 
		return jdbcTemplate.queryForObject(sql, Integer.class);
	}
	public String getCircleName(int circleId) {
		String sql = "SELECT NAME FROM springdata WHERE ID= ?";
		jdbcTemplate.setDataSource(getDataSource());
		return jdbcTemplate.queryForObject(sql, new Object[] {circleId},String.class);
	}
	public DataSource getDataSource() {

		return dataSource;
	}

	@Autowired
	public void setDataSource(DataSource dataSource) {
		jdbcTemplate.setDataSource( dataSource);
		this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
		this.dataSource = dataSource;
	}


	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}


	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	public Circle getCircleforId(int circleId) {
		String sql = "SELECT * FROM springdata WHERE ID = ?";
 
		return jdbcTemplate.queryForObject(sql, new Object[] {circleId}, new CircleMapper());
	}
	public List<Circle> getAllCircles() {
		String sql = "SELECT * FROM springdata";
 
		return jdbcTemplate.query(sql, new CircleMapper());
		
	}
//	public void insertCircle(Circle circle) {
//		String sql = "INSERT INTO springdata (ID, NAME) VALUES (?,?)";
//		jdbcTemplate.update(sql, new Object[] {circle.getId(), circle.getName()} );
//	}
	public void insertCircle(Circle circle){
		  String sql = "INSERT INTO springdata (ID,NAME) VALUES (:id, :name)";
		  Map<String,Object> namedParameters = new HashMap<String,Object>();
		  namedParameters.put("id", circle.getId());
		  namedParameters.put("name", circle.getName());
		  namedParameterJdbcTemplate.update(sql, namedParameters);
	}
	
	
	public void createTriangleTable() {
		String sql = "CREATE TABLE TARIANGLE (ID INTEGER, NAME VARCHAR(50))";
		jdbcTemplate.execute(sql);
	}
	private static final class CircleMapper implements RowMapper<Circle> {

		@Override
		public Circle mapRow(ResultSet resultSet, int rowNum) throws SQLException {
			// TODO Auto-generated method stub
			Circle circle = new Circle();
			circle.setId(resultSet.getInt("ID"));
			circle.setName(resultSet.getString("NAME"));
			return circle;
		}
		
	}
}

• org.zcs.springjdbc.model-Circle.java

package org.zcs.springjdbc.model;

public class Circle {
	public int id;
	public String name;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Circle(int circleId, String name) {
		setId(circleId);
		setName(name);
}
	public Circle() {
		// TODO Auto-generated constructor stub
	}
	public String  toString() {
		
		return this.id + "," + this.name;
	}
	 
}

• spring.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:p="http://www.springframework.org/schema/p"
    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.xsd">

 	<context:annotation-config></context:annotation-config>

 	<context:component-scan base-package="org.zcs.springjdbc"></context:component-scan>

    <!-- other <bean/> definitions here -->
	<bean id = "dataSource" class = "org.springframework.jdbc.datasource.DriverManagerDataSource">
			<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
			<property name="url" value="jdbc:mysql://172.21.3.210:3306/data1?useSSL=false"></property>
			<property name="username" value="MHadoop"></property>
			<property name="password" value="12345678"></property>
	</bean>
</beans>

2.学习记录

• 学习使用了jdbc template的命名参数模式。