Spring学习——JDBC数据库操作

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

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

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

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
          <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
          <property name="url" value="jdbc:mysql://localhost:3306/test"/>
          <property name="username" value="root"/>
          <property name="password" value=""/>
       </bean>

<bean id="userJdbcTemplate" class="com.spring.jdbc.demo.UserJdbcTemplate" >
               
               <property name="dataSource"  ref="dataSource" /> 
       </bean>

**2、核心实现类**

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

**3、JdbcTemplate程序执行**

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

** 4、JdbcTemplate查询操作**

/*方法一重写
    		List<UserPO> list = jdbcTemplate.query(sql,new RowMapper() {
    			
    			public UserPO mapRow(ResultSet rs, int rownum) throws SQLException {
    				UserPO user = new UserPO();
    				user.setId(rs.getInt("id"));
    				user.setPassword(rs.getString("password"));
    				user.setUsername(rs.getString("username"));
    				return user;
    			}
    		});
    		*/

/*方法二自定义
    		List<UserPO> list = jdbcTemplate.query(sql, new UserMapper());
    		*/
    
    
    package com.spring.jdbc.demo;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import org.springframework.jdbc.core.RowMapper;
    
    public class UserMapper implements RowMapper<UserPO>{
    
    	@Override
    	public UserPO mapRow(ResultSet rs, int rownum) throws SQLException {
    		UserPO user = new UserPO();
    		user.setId(rs.getInt("id"));
    		user.setUsername(rs.getString("username"));
    		user.setPassword(rs.getString("password"));
    		return user;
    	}
    	
    }

/*
        使用list<map>查询后转化为目标对象
     */
    List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
    		List<UserPO> list = new ArrayList();
    		for (int i = 0; i < mapList.size(); i++) {
    			UserPO user = new UserPO();
    			user.setId((Integer)mapList.get(i).get("id"));
    			user.setUsername(mapList.get(i).get("username").toString());
    			user.setPassword(mapList.get(i).get("password").toString());
    			list.add(user);
    		}
    		return list;

JDBCBeans.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" 
        xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">
    
       
       <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
          <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
          <property name="url" value="jdbc:mysql://localhost:3306/test"/>
          <property name="username" value="root"/>
          <property name="password" value=""/>
       </bean>
    	
    	
       <bean id="userJdbcTemplate" class="com.spring.jdbc.demo.UserJdbcTemplate" >
       		
       		<property name="dataSource"  ref="dataSource" /> 
       </bean>
          
    
    </beans>

UserJdbcTemplate.java 核心操作类

package com.spring.jdbc.demo;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    import javax.sql.DataSource;
    
    import org.springframework.jdbc.core.JdbcTemplate;
    
    public class UserJdbcTemplate{
    	
    	private JdbcTemplate jdbcTemplate;
    	//@Autowired
    	private DataSource dataSource;
    	
    	//@ConstructorProperties("dataSource")
    	public void setDataSource(DataSource dataSource) {
    		this.dataSource = dataSource;
    		this.jdbcTemplate = new JdbcTemplate(dataSource);
    	}
    	
    	public int createUser(UserPO user) {
    		//System.out.println(user.getUsername()+";"+user.getPassword());
    		String sql = "insert into user(username,password) values(?,?)";
    		int rownum = jdbcTemplate.update(sql,user.getUsername(),user.getPassword());
    		return rownum;
    	}
    
    	
    	public int deleteUserById(int id) {
    		String sql = "delete table user where id = ?";
    		int rownum = jdbcTemplate.update(sql,id);
    		return rownum;
    	}
    
    	
    	public int updateUserById(UserPO user) {
    		String sql ="update user set username=?,password=? where id = ?";
    		int rownum = jdbcTemplate.update(sql, user.getUsername(),user.getPassword(),user.getId());
    		return rownum;
    	}
    
    	
    	public List<UserPO> getUserListAll() {
    		String sql = "select * from user";
    		/*方法一重写
    		List<UserPO> list = jdbcTemplate.query(sql,new RowMapper() {
    			
    			public UserPO mapRow(ResultSet rs, int rownum) throws SQLException {
    				UserPO user = new UserPO();
    				user.setId(rs.getInt("id"));
    				user.setPassword(rs.getString("password"));
    				user.setUsername(rs.getString("username"));
    				return user;
    			}
    		});
    		*/
    		/*方法二自定义
    		List<UserPO> list = jdbcTemplate.query(sql, new UserMapper());
    		*/
    		List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
    		List<UserPO> list = new ArrayList();
    		for (int i = 0; i < mapList.size(); i++) {
    			UserPO user = new UserPO();
    			user.setId((Integer)mapList.get(i).get("id"));
    			user.setUsername(mapList.get(i).get("username").toString());
    			user.setPassword(mapList.get(i).get("password").toString());
    			list.add(user);
    		}
    		return list;
    	}
    
    	
    	public UserPO getUserById(Integer id) {
    		String sql = "select * from user where id = ?";
    		//UserPO user = (UserPO) jdbcTemplate.query(sql,new Object[]{id},new UserMapper());
    		
    		//Object obj =jdbcTemplate.queryForObject(sql, UserPO.class,id);
    		List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql,id);
    		List<UserPO> list = new ArrayList();
    		for (int i = 0; i < mapList.size(); i++) {
    			UserPO user = new UserPO();
    			user.setId((Integer)mapList.get(i).get("id"));
    			user.setUsername(mapList.get(i).get("username").toString());
    			user.setPassword(mapList.get(i).get("password").toString());
    			list.add(user);
    		}
    		return list.get(0);
    	}
    	
    }

PO类

package com.spring.jdbc.demo;
    
    public class UserPO {
    	private int id;
    	private String username;
    	private String password;
    	public int getId() {
    		return id;
    	}
    	public void setId(int id) {
    		this.id = id;
    	}
    	public String getUsername() {
    		return username;
    	}
    	public void setUsername(String username) {
    		this.username = username;
    	}
    	public String getPassword() {
    		return password;
    	}
    	public void setPassword(String password) {
    		this.password = password;
    	}
    	@Override
    	public String toString() {
    		return "UserPO [id=" + id + ", username=" + username + ", password=" + password + "]";
    	}
    	
    }

主函数

package com.spring.jdbc.demo;
    
    import java.util.List;
    
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    
    public class SpringMain {
    	public static void main(String[] args){
    		ApplicationContext context = new ClassPathXmlApplicationContext("JDBCBeans.xml");
    		UserJdbcTemplate ujt = (UserJdbcTemplate) context.getBean("userJdbcTemplate");
    		
    		UserPO user = new UserPO();
    		user.setUsername("lg");
    		user.setPassword("12345");
    		int rownum = ujt.createUser(user);
    		
    		user.setUsername("zdj");
    		user.setPassword("12345");
    		rownum += ujt.createUser(user);
    		
    		user.setUsername("zh");
    		user.setPassword("12345");
    		rownum += ujt.createUser(user);
    		System.out.println("插入条数："+rownum);
    		
    		List<UserPO> list = ujt.getUserListAll();
    		for(UserPO u : list){
    			System.out.println(u.toString());
    		}
    		System.out.println("----------------");
    		user.setId(1);
    		user.setUsername("hm");
    		rownum = ujt.updateUserById(user);
    		user = ujt.getUserById(1);
    		System.out.println(user);
    	}
    }