以User为操作对象

package com.swift.jdbc;public class User {    private Long user_id;        private String    user_code;        private String    user_name;        private String    user_password;        private String    user_state;        public User() {        super();        // TODO Auto-generated constructor stub    }    public User(Long user_id, String user_code, String user_name, String user_password, String user_state) {        super();        this.user_id = user_id;        this.user_code = user_code;        this.user_name = user_name;        this.user_password = user_password;        this.user_state = user_state;    }    public Long getUser_id() {        return user_id;    }    public void setUser_id(Long user_id) {        this.user_id = user_id;    }    public String getUser_code() {        return user_code;    }    public void setUser_code(String user_code) {        this.user_code = user_code;    }    public String getUser_name() {        return user_name;    }    public void setUser_name(String user_name) {        this.user_name = user_name;    }    public String getUser_password() {        return user_password;    }    public void setUser_password(String user_password) {        this.user_password = user_password;    }    public String getUser_state() {        return user_state;    }    public void setUser_state(String user_state) {        this.user_state = user_state;    }    @Override    public String toString() {        return "User [user_id=" + user_id + ", user_code=" + user_code + ", user_name=" + user_name + ", user_password="                + user_password + ", user_state=" + user_state + "]";    }    }

原始JDBC

这个注意ResultSet 是一个带指针的结果集,指针开始指向第一个元素的前一个(首元素),不同于iterator 有hasNext() 和next() ,他只有next()

package com.swift.jdbc;import java.beans.PropertyVetoException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import com.mchange.v2.c3p0.ComboPooledDataSource;public class DemoJDBC {    public static void main(String[] args) throws ClassNotFoundException, SQLException, PropertyVetoException {        findAll();        User user=findById(9l);        System.out.println(user);        user.setUser_name("HanMeimei");        update(user);    }    private static void update(User user) throws PropertyVetoException, SQLException {        ComboPooledDataSource dataSource=new ComboPooledDataSource();        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/crm");        dataSource.setDriverClass("com.mysql.jdbc.Driver");        dataSource.setPassword("root");        dataSource.setUser("root");        dataSource.setMinPoolSize(5);        dataSource.setMaxIdleTime(2000);        Connection con= dataSource.getConnection();        String sql="update sys_user set user_name='"+user.getUser_name()+"' where user_id="+user.getUser_id();        PreparedStatement statement = con.prepareStatement(sql);        statement.executeUpdate();        System.out.println("ok");            }    private static User findById(long id) throws ClassNotFoundException, SQLException {        Class.forName("com.mysql.jdbc.Driver");        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/crm","root","root");        Statement statement = con.createStatement();        ResultSet rs = statement.executeQuery("select * from sys_user where user_id="+id);        User user=null;        while(rs.next()) {            user=new User(rs.getLong("user_id"),rs.getString("user_code"),                    rs.getString("user_name"),rs.getString("user_password"),                    rs.getString("user_state"));        }        return user;            }    private static void findAll() throws ClassNotFoundException, SQLException {        Class.forName("com.mysql.jdbc.Driver");        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/crm?user=root&password=root");        PreparedStatement statement = connection.prepareStatement("select * from sys_user");        ResultSet rs = statement.executeQuery();        List
     
       list=new ArrayList<>();        while(rs.next()) {            Long id = rs.getLong("user_id");            String code=rs.getString("user_code");            String name=rs.getString("user_name");            String password=rs.getString("user_password");            String state=rs.getString("user_state");            User user=new User();            user.setUser_code(code);            user.setUser_id(id);            user.setUser_password(password);            user.setUser_name(name);            user.setUser_state(state);            list.add(user);        }        for(User u:list) {            System.out.println(u);        }        rs.close();        connection.close();    }    }
     

整合c3p0的DBUtils

c3p0整合了连接数据库的Connection ,提供更快速的连接

package com.swift.c3p0;import java.sql.Connection;import java.sql.SQLException;import javax.sql.DataSource;import com.mchange.v2.c3p0.ComboPooledDataSource;public class C3P0Utils {    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();    private static ThreadLocal
     
       thread=new ThreadLocal<>();//    static {//        try {//            dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/crm");//            dataSource.setDriverClass("com.mysql.jdbc.Driver");//            dataSource.setUser("root");//            dataSource.setPassword("root");//            dataSource.setMinPoolSize(5);//        } catch (PropertyVetoException e) {//            e.printStackTrace();//        }//    }        public static DataSource getDataSource() {        return dataSource;    }        public static Connection getConnection() {        Connection con = thread.get();        if(con==null) {            try {                con = dataSource.getConnection();                thread.set(con);                con=thread.get();            } catch (SQLException e) {                e.printStackTrace();            }        }        return con;            }    public static void main(String[] args) {        Connection con = C3P0Utils.getConnection();        System.out.println(con);    }}
     

并可以直接加载xml配置文件,强于(DBCP连接池,他只支持properties文件)

         
              
       
        jdbc:mysql://localhost:3306/crm
               
       
        com.mysql.jdbc.Driver
               
       
        root
               
       
        root
           
      
     

DBUtils的杀手锏QueryRunner 

可以使用?了,放置恶意注意,也提供自动增加' '符号,这个要注意

package com.swift.dbutils;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.BeanHandler;import org.apache.commons.dbutils.handlers.BeanListHandler;import com.swift.c3p0.C3P0Utils;import com.swift.jdbc.User;public class DemoQueryRunner {    private static QueryRunner qr=new QueryRunner(C3P0Utils.getDataSource());    static List
     
       users=new ArrayList
      
       ();        public static void main(String[] args) throws SQLException {        //查找所有        users=findAll();        for(User user:users) {                        System.out.println(user);        }        //通过Id查找        User user=findById(9l);        System.out.println(user);        //更新        user.setUser_name("韩梅梅");        System.out.println("===================================");        System.out.println(user);        update(user);            }    private static void update(User user) throws SQLException {        int update = qr.update(                "update sys_user set user_name=? where user_id=?",user.getUser_name(),user.getUser_id());        System.out.println(user.getUser_name()+update);            }    private static List
       
         findAll() throws SQLException {        List
        
          users = qr.query("select * from sys_user ", new BeanListHandler
         
          (User.class));        return users;    }    private static User findById(long l) throws SQLException {        User user = qr.query(                "select * from sys_user where user_id=?",new BeanHandler
          
           (User.class),l); return user; }}
          
         
        
       
      
     

Hibernate 有个核心配置文件

         
     
         
          
               
       
        com.mysql.jdbc.Driver
               
       
        jdbc:mysql://localhost:3306/crm
               
       
        root
               
       
        root
                       
       
        org.hibernate.dialect.MySQLDialect
                       
       
        org.hibernate.connection.C3P0ConnectionProvider
               
       
        10
               
       
        5
               
       
        5000
                       
       
        update
               
       
        true
               
       
        true
                         
       
        2
                       
               
                       
           
      
     

    
     org.hibernate.connection.C3P0ConnectionProvider
     这个是使用Hibernate自带的c3p0功能,不需要自己写c3p0.xml文件,但除了需要c3p0的jar包,还需要Hibernate自带的jar包,可以在hibernate包的lib->optional->c3p0中找到， 此文件夹中这两个jar：c3p0-0.9.1.jar和hibernate-c3p0-4.2.1.Final.jar都要用,否则异常 Could not instantiate connection provider [org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider]

还有个实体类的映射文件

连接数据库是通过session

package com.swift.hibernate;import java.util.ArrayList;import java.util.List;import org.hibernate.Query;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.hibernate.cfg.Configuration;public class DemoHibernate {    static List
     
       users=new ArrayList
      
       ();    public static void main(String[] args) {        users=findAll();        for(User user:users) {                        System.out.println(user);        }    }    private static List
       
         findAll() {                //new Configuration()是创建Configuration类对象，调用里面的configure方法，读取配置信息，方法返回的又是Configuration类型        Configuration config=new Configuration().configure();        SessionFactory sf = config.buildSessionFactory();        Session session = sf.openSession();        Query query = session.createQuery("from User");        users= query.list();        session.close();        return users;    }}
       
      
     

---

---

---

使用Spring的jdbcTemplate

关于倒包

Spring 总共有20个包看需要导入,其他还有spring的官方扩展包 (整合的较常用的一些,可根据功能导入)

这里只要实现数据库,所以,倒spring的4个核心包beans context core expression 2个日志log4j commons-loging(可不倒)

c3p0要倒 连接数据库mysql-connector-java  还有Spring的spring-jdbc 事务spring-tx 用注解要spring-aop

配置文件菜单法生成

applicationContext.xml(非官方约定俗成的,其实叫什么都可以)

约束如果不想拷贝,可以用菜单导入 window --> preferences -->搜索(xml catalog) -->add-->file system(注意:把location最后的约束文件名复制到key的最后)

导入后

在xml配置文件中,输入Spring的根标签<beans></beans> 然后切换到design视图右击选择 -->namespace-->勾选xsi(这时约束的约束,必须有,控制后面所有)

-->add-->specify new namespace -->location hint (把刚才添加的约束导入)-->把去约束文件名的网址复制到 namespace name -->prefix随便起个名(一般叫约束名

)

最后注意:beans不要有prefix 要设置为空,否则没有标签提示

 

<context:property-placeholder location="classpath:db.properties"/>

扫描src目录下的properties文件使用${key}加载value 据说是spring的el语言spel

感觉这里直接写也可以,单独拿到properties文件里可能不会感觉乱哄哄一堆

测试结果

 

package com.swift.jdbctemplate;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import javax.sql.DataSource;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import org.springframework.jdbc.core.BeanPropertyRowMapper;import org.springframework.jdbc.core.JdbcTemplate;public class DemoJdbcTemplate {    private static ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");    private static DataSource dataSource = (DataSource) context.getBean("dataSource");    private static JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);        public static void main(String[] args) throws SQLException {            User user=new User(null,null,"甲鱼不是龟","456","0");        add(user);        List
     
       users=new ArrayList<>();        users=findAll();        System.out.println(users);        user=findById(23l);        System.out.println(user);    }    private static User findById(long l) {        return jdbcTemplate.queryForObject(                "select * from sys_user where user_id=?", new BeanPropertyRowMapper
      
       (User.class),l);    }    private static List
       
         findAll() {                return jdbcTemplate.query("select * from sys_user", new BeanPropertyRowMapper
        
         (User.class));    }    private static void add(User user) {        jdbcTemplate.update("insert into sys_user values(?,?,?,?,?)",                null,null,user.getUser_name(),user.getUser_password(),user.getUser_state());        System.out.println("插入记录成功");    }}