JDBC(Java Data Base Connectivity,Java数据库连接)
是一种用于执行SQL语句的JavaAPI,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。JDBC 为数据库开发人员提供了一个标准的API,据此可以构建更高级的工具和接口,使数据库开发人员能够用纯JavaAPI编写数据库应用程序,并且可跨平台运行,并且不受数据库供应商的限制。
JDBC使用流程:
在开发环境中加载指定数据库的驱动程序;
driver = "com.mysql.jdbc.Driver"
在Java程序中加载驱动程序;
Class.foName(driver)
创建数据库连接对象;
Connection conn = DriverManager.getConnection(url, user, password);
创建用于执行SQL并接收结果的Statement对象;
PreparedStatement ps = conn.prepareStatement(sql);
通过Statement对象的特定方法执行SQL,并得到结果;
int i = ps.executeUpdate();
关闭数据库连接;
rs.close();
ps.close();
conn.clode();
JDBC的问题
数据库异常问题;
每一次调用数据库连接,都需要重复的打开数据库连接,过度的代码冗余;
频繁的数据库连接,形成过度的资源浪费,影响程序性能;
SQL太死板,不易动态修改SQL,也不利于系统的扩展性与维护性;
结果的封装也比较死板;
SpringJDBC简介
Spring对JDBC做了大量封装,消除了冗余代码,使得开发量大大减小。
Spring 配置<?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:c="http://www.springframework.org/schema/c"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
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/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.3.xsd">
<context:component-scan base-package="com.prosay"/>
<!-- jdbc的配置 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/student?useSSL=true"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
JdbcTemplate用法
execute:可用于执行任何SQL语句,一般用于执行DDL语句; 对于表和数据库的操作
update:用于执行新增、修改、删除等语句;
query:用于执行查询语句;
call:用于执行存储过程、函数相关语句;
自己封装的查询的方法
第一种list存储方式
package com.prosay.jdbc;
import java.lang.reflect.InvocationTargetException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import org.apache.commons.beanutils.BeanUtils;
import org.springframework.jdbc.core.RowMapper;
public class ListMapper<T> implements RowMapper<T>{
private Class<T> clazz;
public ListMapper(Class<T> clazz) {
this.clazz = clazz;
}
public ListMapper() {
}
@Override
public T mapRow(ResultSet rs, int rowNum) throws SQLException {
try {
//获取数据库的元数组 -- 列名和值
ResultSetMetaData md = rs.getMetaData();
T object = clazz.newInstance();
//当前表有多少列
for (int i = 1; i < md.getColumnCount()+1; i++) {
//获取列名转小写
String name = md.getColumnName(i).toLowerCase();
//获取值
Object value = rs.getObject(i);
System.out.println("name:"+name+" value:"+value);
//将数据注入这个字段
if(name!=null && !name.equals("")){
BeanUtils.setProperty(object, name, value);
}
}
return object;
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
return null;
}
}
第二种 map的存储方式
package com.prosay.jdbc;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.BeanUtils;
import org.springframework.jdbc.core.RowMapper;
public class MapMapper implements RowMapper{
@Override
public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
ResultSetMetaData md = rs.getMetaData();
Map<String, Object> map = new HashMap<>();
for (int i = 1; i < md.getColumnCount()+1; i++) {
Object value = rs.getObject(i);
String name = md.getColumnName(i).toLowerCase();
if(name!=null && !name.equals("")){
map.put(name, value);
}
}
return map;
}
}
下面是我的整个工程目录
Student 中和数据库表的列名一致 -- name 和 age
下面是我们的StudentDao类
package com.prosay.dao;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import com.prosay.dao.impl.StudentDaoImpl;
import com.prosay.entity.Student;
import com.prosay.jdbc.ListMapper;
@Component
public class StudentDao implements StudentDaoImpl{
@Autowired
private JdbcTemplate jt;
@Override
public List queryStudent() {
String sql = "select * from student";
//List<Student> list = jt.query(sql, new MapMapper());
List<Student> list = jt.query(sql, new ListMapper(Student.class));
System.out.println(list);
return list;
}
}
StudentService类
package com.prosay.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.prosay.dao.StudentDao;
import com.prosay.dao.impl.StudentDaoImpl;
import com.prosay.entity.Student;
@Service
public class StudentService {
@Autowired
private StudentDaoImpl sd;
public void queryStudent(){
sd.queryStudent();
}
}
Student类
package com.prosay.entity;
import java.util.List;
public class Student {
private String name;
private Integer age;
@Override
public String toString() {
return "Student [name=" + name + ", age=" + age + "]";
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Student(String name, Integer age) {
this.name = name;
this.age = age;
}
public Student() {
}
}
Test类
package com.prosay.test;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.prosay.service.StudentService;
public class Test {
public static void main(String[] args) {
AbstractApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
StudentService ss = (StudentService) app.getBean("studentService");
ss.queryStudent();
}
}