JdbcTest.java
package com.springinaction.jdbc;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
public class JdbcTest {
private JdbcTemplate template;
/**
* 使用JdbcTemplate.update方法出入数据
*
* @param person
* @return
*/
public int insertintoPerson(Person person) {
String sql = "insert into person (id,first_name,last_name) values (?,?,?)";
Object[] params = new Object[] { person.getId(),
person.getFirst_name(), person.getLast_name() };
int[] types = new int[] { Types.INTEGER, Types.VARCHAR, Types.VARCHAR };
return template.update(sql, params,types);
}
/**
* 使用BatchPreparedStatementSetter插入多条数据.
*
* @param persons
* @return
*/
public int[] upatePersons(final List
persons) {
String sql = "insert into person (id,first_name,last_name) values (?,?,?)";
BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
public int getBatchSize() {
return persons.size();// 定义批量语句的数量
}
public void setValues(PreparedStatement ps, int i)
throws SQLException {
Person person = (Person) persons.get(i);
ps.setInt(1, person.getId());
ps.setString(2, person.getFirst_name());
ps.setString(3, person.getLast_name());
}
};
return template.batchUpdate(sql, setter);
}
public JdbcTemplate getTemplate() {
return template;
}
public void setTemplate(JdbcTemplate template) {
this.template = template;
}
/**
* 使用RorCallbackHandler执行一个查询
*
* @param id
* @return
*/
public Person getPerson(final int id) {
final Person person = new Person();
String sql = "select id,first_name,last_name from person where id = ? ";
final Object[] params = new Object[] { id };
template.query(sql, params, new RowCallbackHandler() {
public void processRow(ResultSet rs) throws SQLException {
person.setId(rs.getInt("id"));
person.setFirst_name(rs.getString("first_name"));
person.setLast_name(rs.getString("last_name"));
}
});
return person;
}
/**
* 使用RowMapper执行一个查询
*
* @param id
* @return
*/
public Person getPeron2(final int id) {
String sql = "select id,first_name,last_name from person where id = ? ";
final Object[] params = new Object[] { id };
List list = template.query(sql, params, new PersonRowMapper());
return (Person) list.get(0);
}
/**
* 使用RowMapper执行获取所有数据.
*
* @return
*/
public List getAllPersons() {
String sql = "select * from person";
return template.query(sql, new PersonRowMapper());
}
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext(
"com/springinaction/jdbc/conf.xml");
JdbcTemplate template = (JdbcTemplate) context.getBean("template");
JdbcTest test = new JdbcTest();
test.setTemplate(template);
// Person person = new Person();
// person.setId(2);
// person.setFirst_name("张");
// person.setLast_name("彦星");
//
//
// test.insertintoPerson(person);
// List persons = new ArrayList();
// for(int i=0;i<10;i++)
// {
// final Person person = new Person();
// person.setId(i);
// person.setFirst_name("" + i);
// person.setLast_name("" + i);
// persons.add(person);
// }
//
// test.upatePersons(persons);
final int i = 3;
System.out.println(test.getPerson(i));
// List list = test.getAllPersons();
// for(int i=0;i< list.size();i++)
// {
// Person person = (Person) list.get(i);
// System.out.println(person);
// }
// System.out.println(test.getPeron2(3));
}
}
Person.java
package com.springinaction.jdbc;
public class Person {
private int id;
private String first_name;
private String last_name;
public String getFirst_name() {
return first_name;
}
public void setFirst_name(String first_name) {
this.first_name = first_name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getLast_name() {
return last_name;
}
public void setLast_name(String last_name) {
this.last_name = last_name;
}
public String toString() {
return "person ID " + this.id + "\n name is " + this.first_name + " "
+ this.last_name;
}
}
PersonRowMapper.java
package com.springinaction.jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class PersonRowMapper implements RowMapper {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
// TODO Auto-generated method stub
Person person = new Person();
person.setId(rs.getInt("id"));
person.setFirst_name(rs.getString("first_name"));
person.setLast_name(rs.getString("last_name"));
return person;
}
}
conf.xml
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
com/springinaction/jdbc/jdbc.properties
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
${database.url}
${database.driver}
${database.user}
${database.password}
jdbc.properties
database.url=jdbc:mysql://localhost/smaple
database.driver=org.gjt.mm.mysql.Driver
database.user=root
database.password=root
createTable.sql
CREATE TABLE `person` (
`id` int(11) DEFAULT NULL,
`first_name` varchar(20) DEFAULT NULL,
`last_name` varchar(20) DEFAULT NULL
)
阅读(1075) | 评论(0) | 转发(0) |
