java 对数据库的基本操作

1.数据库连接

通过读取配置文件的方式来获取连接的一些数据信息，提高它的可维护性。

定义数据库配置文件：jdbc.properties

driver=com.mysql.jdbc.Driver  
url=jdbc:mysql://localhost:3306/hncu?useUnicode=true&characterEncoding=UTF-8  
username=root  
password=1234  
  
## Oracle  
#driver=oracle.jdbc.driver.OracleDriver  
#url=jdbc:oracle:thin:@192.168.31.12:1521:orcl  
#username=scott  
#password=tiger

然后创建一个工具类，供多处使用：

public class ConnFactory {  
    private static Connection conn;  
    static {  
        try {  
            //读取配置文件  
            Properties p = new Properties();  
            p.load(ConnFactory.class.getClassLoader().getResourceAsStream("jdbc.properties"));  
            String driver = p.getProperty("driver");  
            String url = p.getProperty("url");  
            String username = p.getProperty("username");  
            String pwd = p.getProperty("password");  
              
            Class.forName(driver);  
            conn = DriverManager.getConnection(url,username,pwd);  
            System.out.println("已连接到数据库..."+conn);  
        } catch (Exception e) {  
            throw new RuntimeException("读取配置文件失败", e);  
        }  
    }  
    public static Connection getConn(){  
        return conn;  
    }  
    public static void main(String[] args) {  
        getConn();  
    }  
}  

2.执行一次简单的查询

public void getXXXDemo() throws Exception{  
        Statement st = ConnFactory.getConn().createStatement();  
        String sql = "select * from book";  
        ResultSet rs = st.executeQuery(sql);  
        while(rs.next()){  
            Integer id = rs.getInt(1);//这里的1表示数据表中的第一列，下面同理  
            String name = rs.getString(2);  
            //Double price = (Double)rs.getObject(3);//出异常，因为内部是采用BigDecimal来处理  
            Double price = rs.getDouble(3);  
            Object dateTime = rs.getObject(4);//把日期和时间作为一个整体读取出来  
            System.out.println(id+","+name+","+price+","+dateTime);  
              
            String strDateTime = dateTime.toString();  
            System.out.println(strDateTime);  
            strDateTime = rs.getDate(4)+"--"+rs.getTime(4);//日期和时间可以单独获取  
            System.out.println(":::"+strDateTime);  
        }  
        ConnFactory.getConn().close();  
    }

3.关于statement的几个方法

1、executeQuery: 只能执行select语句

2、executeUpdate: 可以执行insert、delete和update语句，但不能执行select

3、execute:增删改查的4种(任意)语句都能执行。该方法若执行非select语句时返回false，执行select语句时返回true，且st对象会缓存该次查询的结果，我们可通过ResultSet rs = st.getResultSet()来获得结果集

public void executeXXXDemo() throws Exception{  
        Statement st = ConnFactory.getConn().createStatement();  
        String sql = "select * from book";  
        //String sql = "insert into book(name,price,pub) values('软件工程',22.35,'2015-12-05 22:12:23')";  
        //String sql = "update book set price=38.88 where name='软件工程'";  
        //String sql = "delete from book where name='软件工程'";  
        //st.executeQuery(sql);  
        //st.executeUpdate(sql);  
        boolean boo = st.execute(sql);  
          
        if(boo){  
            ResultSet rs = st.getResultSet();  
            while(rs.next()){  
                System.out.println(rs.getObject(2));  
            }  
        }  
    }

4.获取自动增长的列

 public void saveAuto() throws Exception{  
        Connection con = ConnFactory.getConn();  
        String sql = "insert into book(name,price,pub) values('JavaEE',100.8,'2013-06-12 08:30:30')";  
        Statement st = con.createStatement();  
          
        //st.executeUpdate(sql);  
        st.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);  
        ResultSet rs = st.getGeneratedKeys();//里面封装了自动生成的所有值  
        if(rs.next()){  
            int id = rs.getInt(1);//获取第1个自动增长列  
            System.out.println("自动增长的id:"+id);  
        }  
        System.out.println("-----------------");  
          
        //预处理语句  
        sql =  "insert into book(name,price) values(?,?)";  
        PreparedStatement pst = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);  
        pst.setString(1, "计算机基础");  
        pst.setDouble(2, 28);  
        pst.executeUpdate();  
        ResultSet rs2 = pst.getGeneratedKeys();//里面封装了自动生成的所有值  
        if(rs2.next()){  
            int id = rs2.getInt(1);//获取第1个自动增长列  
            System.out.println("自动增长的id:"+id);  
        }  
    }

5 简单的批处理sql

    public void batch() throws Exception{  
            Connection con = ConnFactory.getConn();  
            String sql = "insert into book(name,price,pub) values('JavaEE',100.8,'2015-06-12 08:30:30')";  
            Statement st = con.createStatement();  
            for(int i=0;i<10;i++){  
                st.addBatch(sql);  
            }  
            sql = "update book set price=price*1.1 where price>100";  
            st.addBatch(sql);  
            int[] a = st.executeBatch();  
            for(int r:a){  
                System.out.println(r);//r为每条sql语句所影响的记录数  
            }  
        }  

    public void preparedBatch() throws Exception{  
            Connection con = ConnFactory.getConn();  
            String sql = "insert into book(name,price,pub) values(?,?,?)";  
            PreparedStatement pst = con.prepareStatement(sql);  
            for(int i=0;i<5;i++){  
                pst.setString(1, "Java"+i);  
                pst.setDouble(2, 55.85+i);  
                pst.setString(3, "2016-12-10 07:07:08");  
                pst.addBatch(); //pst的方式不能带参数sql  
            }  
            //pst.executeBatch();  
            int[] a = pst.executeBatch();  
            for(int r:a){  
                System.out.println(r);//r为每条sql语句所影响的记录数  
            }  
        }  

转自 查看原文