数据库

位置:IT落伍者 >> 数据库 >> 浏览文章

Spring中使用JDBC


发布日期:2020年08月12日
 
Spring中使用JDBC
首先要获得DataSource连接池

要对数据库执行任何的JDBC操作需要有一个Connection在Spring中Connection对象是通过DataSource获得的

有几种方法可以得到DataSource 其中一种方法是使用Spring提供的轻量级orgspringframeworkjdbcdatasourceDriverManagerDataSource第二种方法是使用monsdbcpBasicDataSource类

使用DriverMangerDataSource这种方法是轻量级的方便测试

public class DataSoureProvider {

public static DriverManagerDataSource dataSource = new DriverManagerDataSource()

public static DriverManagerDataSource getInstance() {

dataSourcesetDriverClassName(commysqljdbcDriver

dataSourcesetUrl(jdbc:mysql://localhost:/book

dataSourcesetUsername(y****

dataSourcesetPassword(h*******

return dataSource;

}

@Test

public void test() {

DataSoureProvidergetInstance()

try {

dataSourcegetConnection()

}

catch (SQLException e) {

eprintStackTrace()

}

} }

~行是配置连接数据库所需的信息

使用BasicDataSouce创建一个连接池应为BasicDataSource所有属性都是通过setter方法暴露在外面的我们可以像配置其他Srping Bean那样配置它

我将数据库连接信息配置在properties文件中利用spring的orgspringframewonfigPropertyPlaceholderConfigurer类进行读取装载可以查看spring_装配Bean一文

书写配置文件applicationContextxml:

<?xml version= encoding=UTF?> <beans

xmlns=

xmlns:xsi=instance

xmlns:p=

xsi:schemaLocation= beansxsd>

<bean id=dbproperty class=orgspringframewonfigPropertyPlaceholderConfigurer>

<property name=location>

<value>connectproperties</value>

</property>

</bean>

<bean id=myDataSource class=monsdbcpBasicDataSource>

<property name=driverClassName>

<value>${dbdriver}</value>

</property>

<property name=url>

<value>${dburl}</value>

</property>

<property name=username>

<value>${dbusername}</value>

</property>

<property name=password>

<value>${dbpassword}</value>

</property>

</bean> </beans>

~行配置BasicDataSource参数其中<value>中的参数是在connectpropertices配置文件中拿到的

进行测试

public class DataSourceProvider {

@Test

public void connectTest() {

ApplicationContext context = new ClassPathXmlApplicationContext(

applicationContextxml

BasicDataSource dataSource = (BasicDataSource) context

getBean(myDataSource

try {

dataSourcegetConnection()

Systemoutprintln(connect successful

}

catch (SQLException e) {

eprintStackTrace()

}

}

}

使用monsdbcpBasicDataSource需要引入额外的jar包分别是commonscollectionsjarcommonsdbcpjarcommonspooljar为了方便大家这里有这三个jar包的下载地址

Spring把JDBC中重复的操作建立成了一个模板类orgsprireJdbcTemplate

使用JdbcTemplate:

要使用JdbcTemplate需要为每一个DAO配置一个JdbcTemplate实例

public class StudentDaoImp implements StudentDao {

private JdbcTemplate jdbcTemplate;

@Override

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

thisjdbcTemplate = jdbcTemplate;

} }

如上StudentDaoImp内配置了一个JdbcTemplate对象和它对应的setter方法这样就可以在Spring配置文件中对其进行赋值

<?xml version= encoding=UTF?> <beans xmlns=

xmlns:xsi=instance xmlns:p=

xsi:schemaLocation= beansxsd>

<bean id=dbproperty

class=orgspringframewonfigPropertyPlaceholderConfigurer>

<property name=location>

<value>connectproperties</value>

</property>

</bean>

<bean id=myDataSource class=monsdbcpBasicDataSource>

<property name=driverClassName>

<value>${dbdriver}</value>

</property>

<property name=url>

<value>${dburl}</value>

</property>

<property name=username>

<value>${dbusername}</value>

</property>

<property name=password>

<value>${dbpassword}</value>

</property>

</bean>

<bean id=jdbcTemplate class=orgsprireJdbcTemplate>

<property name=dataSource>

<ref bean=myDataSource/>

</property>

</bean>

<bean id=studentDao class=comsunflowerdaoStudentDaoImp>

<property name=jdbcTemplate>

<ref bean=jdbcTemplate/>

</property>

</bean> </beans>

~行是装配JdbcTemplate这个Bean其中需要为其设置dataSource这个参数就是我们上面的到的DataSource

使用JdbcTemplate插入数据

public class StudentDaoImp implements StudentDao {

private JdbcTemplate jdbcTemplate;

@Override

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

thisjdbcTemplate = jdbcTemplate;

}

public void insert(Student student)

{

String sql = insert into student (cnonamescore) values(???);

//设置传递给通配符的参数

Object[] params = new Object[]{studentgetCno() studentgetName() studentgetScore()};

jdbcTemplateupdate(sql params)

} }

~行为插入一条学生记录的方法行中JdbcTemplate为我们提供了update(String sqlObject… args)方法方便我们进行数据的插入

进行测试

public class InsertTest {

@Test

public void insertTest() {

Student student = new Student()

studentsetCno(

studentsetName(张飞

studentsetScore(

ApplicationContext context = new ClassPathXmlApplicationContext(

applicationContextxml

StudentDaoImp studentDao = (StudentDaoImp) contextgetBean(studentDao

studentDaoinsert(student)

} }

数据库中多了一条记录

mysql> select * from student;

+++++

| sno | cno | name | score |

+++++

| | | 地心 | |

| | | 华雄 | |

| | | 孝慈 | |

| | | 必须 | |

| | | 华雄 | |

| | | 地心 | |

| | | 横切 | |

| | | 横切 | |

| | | 横切 | |

| | | 张飞 | |

+++++

rows in set

批量插入数据

批量插入数据需要用到orgsprireBatchPreparedStatementSetter接口

修改StudentDaoImp:

public class StudentDaoImp implements StudentDao {

private JdbcTemplate jdbcTemplate;

@Override

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

thisjdbcTemplate = jdbcTemplate;

}

public int insert(Student student)

{

String sql = insert into student (cnonamescore) values(???);

//设置传递给通配符的参数

Object[] params = new Object[]{studentgetCno() studentgetName() studentgetScore()};

return jdbcTemplateupdate(sql params)

}

public int[] batchInsert(final List<Student> list)

{

String sql = insert into student (cnonamescore) values(???);

BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {

@Override

public void setValues(PreparedStatement ps int index) throws SQLException {

Student student = (Student) listget(index)

pssetInt( studentgetCno())

pssetString( studentgetName())

pssetDouble( studentgetScore())

}

//有多少条记录要处理

@Override

public int getBatchSize() {

return listsize()

}

};

return jdbcTemplatebatchUpdate(sql setter)

} }

~行为批量插入的方法BatchPreparedStatementSetter接口的两个方法其中getBatchSize()方法是得到需要插入的记录的个数setValues(PreparedStatement ps int index)方法是实际进行插入的方法

进行测试

@Test

public void batchInsert() {

Student student = null student = null student = null;

student = new Student()

student = new Student()

student = new Student()

studentsetCno(

studentsetName(刘备

studentsetScore(

studentsetCno(

studentsetName(关羽

studentsetScore(

studentsetCno(

studentsetName(张飞

studentsetScore(

List<Student> list = new ArrayList<Student>()

listadd(student

listadd(student

listadd(student

ApplicationContext context = new ClassPathXmlApplicationContext(

applicationContextxml

StudentDaoImp studentDao = (StudentDaoImp) context

getBean(studentDao

studentDaobatchInsert(list)

}

插入结果

mysql> select * from student;

+++++

| sno | cno | name | score |

+++++

| | | 刘备 | |

| | | 关羽 | |

| | | 张飞 | |

+++++

rows in set

查询一条记录

执行一条数据的查询需要使用orgsprireRowCallbackHandler接口的实现

修改StudentDaoImp:

/**

* 查询一条记录

*/

public Student getStudent(final int id) {

// 装载查询结果

final Student student = new Student()

String sql = select osnamesscore from student s where sno = ?;

// 设置查询参数

final Object[] params = new Object[] { new Integer(id) };

// 进行查询

jdbcTemplatequery(sql params new RowCallbackHandler() {

@Override

public void processRow(ResultSet rs) throws SQLException {

studentsetCno(rsgetInt(cno))

studentsetName(rsgetString(name))

studentsetScore(rsgetDouble(score))

}

})

return student;

}

进行测试

@Test

public void selectTest() {

ApplicationContext context = new ClassPathXmlApplicationContext(

applicationContextxml

StudentDaoImp studentDao = (StudentDaoImp) context

getBean(studentDao

Student student = studentDaogetStudent(

Systemoutprintln(cno: + studentgetCno() + name:+ studentgetName() + score: + studentgetScore())

}

查询多条记录

这里需要用到orgsprireRowMapper接口的实现

修改StudentDaoImp:

/**

* 查询多条记录

*/

public List<Student> getAllStudent() {

String sql = select osnamesscore from student s;

return jdbcTemplatequery(sql new RowMapper<Student>() {

@Override

public Student mapRow(ResultSet rs int index) throws SQLException {

Student student = new Student()

studentsetCno(rsgetInt(cno))

studentsetName(rsgetString(name))

studentsetScore(rsgetDouble(score))

return student;

}

})

}

RowMapper接口负责把Result中的一条记录映射成一个对象

进行测试

@Test

public void getAllStudent() {

ApplicationContext context = new ClassPathXmlApplicationContext(

applicationContextxml

StudentDaoImp studentDao = (StudentDaoImp) context

getBean(studentDao

List<Student> list = new ArrayList<Student>()

list = studentDaogetAllStudent()

for (int i = ; i < listsize() i++) {

Systemoutprintln(name is: + listget(i)getName())

}

}

也可以使用这种方法查询一条记录只要附加查询参数即可

/**

* 查询一条记录

*/

public Student getStudent(final int id) {

// 装载查询结果

final Student student = new Student()

String sql = select osnamesscore from student s where sno = ?;

// 设置查询参数

final Object[] params = new Object[] { new Integer(id) };

List<Student> list = jdbcTemplatequery(sql params

new RowMapper<Student>() {

@Override

public Student mapRow(ResultSet rs int index)

throws SQLException {

Student student = new Student()

studentsetCno(rsgetInt(cno))

studentsetName(rsgetString(name))

studentsetScore(rsgetDouble(score))

return student;

}

})

return listget(

}

               

上一篇:在Oracle中使用Java存储过程

下一篇:专家讲解优化Derby数据库程序性能