使用SpringJdbc操作数据实例

妖狐艹你老母 2023-03-12 12:24 6阅读 0赞

一 .使用SpringJdbc操作数据实例  
1.编写druid数据库连接池工具：  
(1)导入druid包：  
(2)创建jdbc.properties数据库配置文件

driverClassName=com.mysql.jdbc.Driver
    url=jdbc:mysql://localhost:3306/db_tt
    username=root
    password=root
    # 初始化连接数量
    initialSize=5
    # 最大连接数
    maxActive=10
    # 最大等待时间
    maxWait=3000

(3)编写DruidJdbcUtil工具类：

/** * Druid连接池的工具类 */
    public class DruidJdbcUtil { 
        //1.定义成员变量 DataSource
        private static DataSource ds ;
        static{ 
            try { 
                //1.加载配置文件
                Properties pro = new Properties();
                pro.load(DruidJdbcUtil.class.getClassLoader().getResourceAsStream("druid.properties"));
                //2.获取DataSource
                ds = DruidDataSourceFactory.createDataSource(pro);
            } catch (IOException e) { 
                e.printStackTrace();
            } catch (Exception e) { 
                e.printStackTrace();
            }
        }
    
        //1.获取连接
        public static Connection getConnection() throws SQLException { 
        	return ds.getConnection();  
        }
    
        // 2.获取连接池方法
        public static DataSource getDataSource(){ 
        	return  ds;   
        }
        //3.释放资源
        public static void close(Statement stmt,Connection conn){ 
        	close(null,stmt,conn);   
        }
        public static void close(ResultSet rs , Statement stmt, Connection conn){ 
            if(rs != null){ 
                try { 
                    rs.close();
                } catch (SQLException e) {  e.printStackTrace(); }
            }
            if(stmt != null){ 
                try { 
                    stmt.close();
                } catch (SQLException e) {    e.printStackTrace();  }
            }
            if(conn != null){ 
                try { 
                    conn.close();//归还连接
                } catch (SQLException e) {  e.printStackTrace();  }
            }
        }
    }

2.使用SpringJdbc操作数据库：  
(1)导入spring-jdbc包：  
(2)编写SpringJdbcUtil工具类：

public class SpringJdbcUtil { 
       public static void main(String[] args) { 
    	   //获取JdbcTemplate对象
    	   JdbcTemplate template = new JdbcTemplate(DruidJdbcUtil.getDataSource());
    	   
    	   //1.queryForObject
    	   String sql ="select count(id) from job_info ";
    	   Long l = template.queryForObject(sql, Long.class);
    	   System.out.println(l);
    	   
    	   //2.update
           sql = "update emp set salary = 10000 where id = 1001";
           template.update(sql);
           sql = "insert into emp(id,ename,dept_id) values(?,?,?)";
           template.update(sql, 1015, "郭靖", 10);
           sql = "delete from emp where id = ?";
           template.update(sql, 1015);
           
           //3.queryForMap
           sql = "select * from emp where id = ? or id = ?";
           Map<String, Object> map = template.queryForMap(sql, 1001,1002);
           
           //4.queryForList
           sql = "select * from emp";
           List<Map<String, Object>> list = template.queryForList(sql);
           
           //5.query
           sql = "select * from emp";
           List<Emp> list1 = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
       }
    }