Spring学习——JDBC数据库操作

爱被打了一巴掌 2022-05-13 11:34 307阅读 0赞

JDBC Java数据库连接(Java Data Base Connectivity)，是一种执行SQL语句的API，提供关系型数据库的统一访问

1、配置beans.xml配置文件
需要jar包 spring-jdbc-release.jar 、mysql-connect-java.jar 

  1. <!-- 初始化数据库配置文件 -->
  2.    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  3.       <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  4.       <property name="url" value="jdbc:mysql://localhost:3306/test"/>
  5.       <property name="username" value="root"/>
  6.       <property name="password" value=""/>
  7.    </bean>
  11.  <!-- 注册jdbc核心实现类 -->
  12.    <bean id="userJdbcTemplate" class="com.spring.jdbc.demo.UserJdbcTemplate" >
  13.            <!-- 注入数据库配置文件 不然在实现时报空指针-->
  14.            <property name="dataSource"  ref="dataSource" /> 
  15.    </bean>

2、核心实现类

  1. private JdbcTemplate jdbcTemplate;
  3.     private DataSource dataSource;
  4.     //JdbcTemplate 需要提供jdbc配置数据，否则使用时空指针异常
  5.     public void setDataSource(DataSource dataSource) {
  6.         this.dataSource = dataSource;
  7.         this.jdbcTemplate = new JdbcTemplate(dataSource);
  8.     }

3、JdbcTemplate程序执行

  1. 1>JdbcTemplate操作数据库分两种:更新数据库(insert,update,delete)和查询数据库(select)  
  2. 2>sql语句,可以使用"id = ?" 和  "id = "+ id 两种  
  3.     "id = ?"    在jdbcTemplate执行操作时,需要另赋值;  
  4.     "id = "+ id 只需要执行SQL便可

4、JdbcTemplate查询操作

  1. /*方法一重写
  2.         List<UserPO> list = jdbcTemplate.query(sql,new RowMapper() {
  4.             public UserPO mapRow(ResultSet rs, int rownum) throws SQLException {
  5.                 UserPO user = new UserPO();
  6.                 user.setId(rs.getInt("id"));
  7.                 user.setPassword(rs.getString("password"));
  8.                 user.setUsername(rs.getString("username"));
  9.                 return user;
  10.             }
  11.         });
  12.         */
  14. /*方法二自定义
  15.         List<UserPO> list = jdbcTemplate.query(sql, new UserMapper());
  16.         */
  19. package com.spring.jdbc.demo;
  21. import java.sql.ResultSet;
  22. import java.sql.SQLException;
  24. import org.springframework.jdbc.core.RowMapper;
  26. public class UserMapper implements RowMapper<UserPO>{
  28.     @Override
  29.     public UserPO mapRow(ResultSet rs, int rownum) throws SQLException {
  30.         UserPO user = new UserPO();
  31.         user.setId(rs.getInt("id"));
  32.         user.setUsername(rs.getString("username"));
  33.         user.setPassword(rs.getString("password"));
  34.         return user;
  35.     }
  37. }
  39. /*
  40.     使用list<map>查询后转化为目标对象
  41.  */
  42. List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
  43.         List<UserPO> list = new ArrayList();
  44.         for (int i = 0; i < mapList.size(); i++) {
  45.             UserPO user = new UserPO();
  46.             user.setId((Integer)mapList.get(i).get("id"));
  47.             user.setUsername(mapList.get(i).get("username").toString());
  48.             user.setPassword(mapList.get(i).get("password").toString());
  49.             list.add(user);
  50.         }
  51.         return list;

JDBCBeans.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans xmlns="http://www.springframework.org/schema/beans"
  3.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  4.     xsi:schemaLocation="http://www.springframework.org/schema/beans
  5.     http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">
  7.    <!-- 初始化数据库配置文件 -->
  8.    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  9.       <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  10.       <property name="url" value="jdbc:mysql://localhost:3306/test"/>
  11.       <property name="username" value="root"/>
  12.       <property name="password" value=""/>
  13.    </bean>
  15.     <!-- 配置jdbc实现类 -->
  16.    <bean id="userJdbcTemplate" class="com.spring.jdbc.demo.UserJdbcTemplate" >
  17.            <!-- 注入数据库配置文件 -->
  18.            <property name="dataSource"  ref="dataSource" /> 
  19.    </bean>
  22. </beans>

UserJdbcTemplate.java 核心操作类

  1. package com.spring.jdbc.demo;
  3. import java.util.ArrayList;
  4. import java.util.List;
  5. import java.util.Map;
  7. import javax.sql.DataSource;
  9. import org.springframework.jdbc.core.JdbcTemplate;
  11. public class UserJdbcTemplate{
  13.     private JdbcTemplate jdbcTemplate;
  14.     //@Autowired
  15.     private DataSource dataSource;
  17.     //@ConstructorProperties("dataSource")
  18.     public void setDataSource(DataSource dataSource) {
  19.         this.dataSource = dataSource;
  20.         this.jdbcTemplate = new JdbcTemplate(dataSource);
  21.     }
  23.     public int createUser(UserPO user) {
  24.         //System.out.println(user.getUsername()+";"+user.getPassword());
  25.         String sql = "insert into user(username,password) values(?,?)";
  26.         int rownum = jdbcTemplate.update(sql,user.getUsername(),user.getPassword());
  27.         return rownum;
  28.     }
  31.     public int deleteUserById(int id) {
  32.         String sql = "delete table user where id = ?";
  33.         int rownum = jdbcTemplate.update(sql,id);
  34.         return rownum;
  35.     }
  38.     public int updateUserById(UserPO user) {
  39.         String sql ="update user set username=?,password=? where id = ?";
  40.         int rownum = jdbcTemplate.update(sql, user.getUsername(),user.getPassword(),user.getId());
  41.         return rownum;
  42.     }
  45.     public List<UserPO> getUserListAll() {
  46.         String sql = "select * from user";
  47.         /*方法一重写
  48.         List<UserPO> list = jdbcTemplate.query(sql,new RowMapper() {
  50.             public UserPO mapRow(ResultSet rs, int rownum) throws SQLException {
  51.                 UserPO user = new UserPO();
  52.                 user.setId(rs.getInt("id"));
  53.                 user.setPassword(rs.getString("password"));
  54.                 user.setUsername(rs.getString("username"));
  55.                 return user;
  56.             }
  57.         });
  58.         */
  59.         /*方法二自定义
  60.         List<UserPO> list = jdbcTemplate.query(sql, new UserMapper());
  61.         */
  62.         List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
  63.         List<UserPO> list = new ArrayList();
  64.         for (int i = 0; i < mapList.size(); i++) {
  65.             UserPO user = new UserPO();
  66.             user.setId((Integer)mapList.get(i).get("id"));
  67.             user.setUsername(mapList.get(i).get("username").toString());
  68.             user.setPassword(mapList.get(i).get("password").toString());
  69.             list.add(user);
  70.         }
  71.         return list;
  72.     }
  75.     public UserPO getUserById(Integer id) {
  76.         String sql = "select * from user where id = ?";
  77.         //UserPO user = (UserPO) jdbcTemplate.query(sql,new Object[]{id},new UserMapper());
  79.         //Object obj =jdbcTemplate.queryForObject(sql, UserPO.class,id);
  80.         List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql,id);
  81.         List<UserPO> list = new ArrayList();
  82.         for (int i = 0; i < mapList.size(); i++) {
  83.             UserPO user = new UserPO();
  84.             user.setId((Integer)mapList.get(i).get("id"));
  85.             user.setUsername(mapList.get(i).get("username").toString());
  86.             user.setPassword(mapList.get(i).get("password").toString());
  87.             list.add(user);
  88.         }
  89.         return list.get(0);
  90.     }
  92. }

PO类

  1. package com.spring.jdbc.demo;
  3. public class UserPO {
  4.     private int id;
  5.     private String username;
  6.     private String password;
  7.     public int getId() {
  8.         return id;
  9.     }
  10.     public void setId(int id) {
  11.         this.id = id;
  12.     }
  13.     public String getUsername() {
  14.         return username;
  15.     }
  16.     public void setUsername(String username) {
  17.         this.username = username;
  18.     }
  19.     public String getPassword() {
  20.         return password;
  21.     }
  22.     public void setPassword(String password) {
  23.         this.password = password;
  24.     }
  25.     @Override
  26.     public String toString() {
  27.         return "UserPO [id=" + id + ", username=" + username + ", password=" + password + "]";
  28.     }
  30. }

主函数

  1. package com.spring.jdbc.demo;
  3. import java.util.List;
  5. import org.springframework.context.ApplicationContext;
  6. import org.springframework.context.support.ClassPathXmlApplicationContext;
  8. public class SpringMain {
  9.     public static void main(String[] args){
  10.         ApplicationContext context = new ClassPathXmlApplicationContext("JDBCBeans.xml");
  11.         UserJdbcTemplate ujt = (UserJdbcTemplate) context.getBean("userJdbcTemplate");
  13.         UserPO user = new UserPO();
  14.         user.setUsername("lg");
  15.         user.setPassword("12345");
  16.         int rownum = ujt.createUser(user);
  18.         user.setUsername("zdj");
  19.         user.setPassword("12345");
  20.         rownum += ujt.createUser(user);
  22.         user.setUsername("zh");
  23.         user.setPassword("12345");
  24.         rownum += ujt.createUser(user);
  25.         System.out.println("插入条数："+rownum);
  27.         List<UserPO> list = ujt.getUserListAll();
  28.         for(UserPO u : list){
  29.             System.out.println(u.toString());
  30.         }
  31.         System.out.println("----------------");
  32.         user.setId(1);
  33.         user.setUsername("hm");
  34.         rownum = ujt.updateUserById(user);
  35.         user = ujt.getUserById(1);
  36.         System.out.println(user);
  37.     }
  38. }

发表评论

表情：
评论列表 （有 0 条评论，307人围观）

还没有评论，来说两句吧...

相关阅读

    相关 JDBC操作SQLite数据库

    SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能

    秒速五厘米秒速五厘米/ 0 赞/ 891 阅读