java基础--JDBC

JDBC:一堆接口

需掌握的接口：

connection

   

Statement                数据库服务器执行sql

 

ResultSet     返回结构

 

//1.装载oracleDriver

Class.forName("oracle.jdbc.OracleDriver");

        

/**2.创建connection的实现对象

     DriverManager类

 * */

String url ="jdbc:oracle:thin:@192.168.0.200:1521:tarena";

String user ="jsd1303_1";

String password ="jsd1303_1";

Connection con =DriverManager.getConnection(url, user, password);

System.out.println(con);

        

/**3. 通过statement对象，传送sql语句

 *   stmt是实现了 statement接口的对象

 * */

Statement stmt = con.createStatement();

System.out.println(stmt);

        

//executeQuery执行查询

ResultSet rs = stmt.executeQuery(

"selectid,real_name,create_date from slf_account");

//从ResultSet中获得查询到的数据      

while(rs.next()){

    int id = rs.getInt("id");//获得id的值转化为int类型

    String real_name = rs.getString(2);//获得第二列的数据

    Date create_date =rs.getDate("create_date");

    System.out.println(id+""+real_name+" "+create_date);

}

rs.close();

stmt.colse();

con.close();

 

 

修改列名account表中s2 改为ss2

alter table account rename columns2 to ss2;

 

day520(jdbc)

//1.装载oracleDriver

Class.forName("oracle.jdbc.OracleDriver");

/**2.创建connection的实现对象

 DriverManager类

* */

String url ="jdbc:oracle:thin:@192.168.0.200:1521:tarena";

String user ="jsd1303_1";

String password ="jsd1303_1";

Connection con = DriverManager.getConnection(url,user, password);

/**3. 通过statement对象，传送sql语句

 * stmt是实现了    statement接口的对象

 * */

Statementstmt = con.createStatement();//不安全，可能出现sql注入，建议使用PreparedStatement

例如：String sql = "select * from slf_account wherelogin_name="+"'"+loginname+"'"+"andlogin_passwd="+"'"+loginPassword+"'";

//executeQuery执行查询

ResultSet rs =stmt.executeQuery("select id,real_name,create_date fromslf_account");

//从ResultSet中获得查询到的数据      

while(rs.next()){

     intid = rs.getInt("id");//获得id的值转化为int类型

     Stringreal_name = rs.getString(2);//获得第二列的数据

     Datecreate_date = rs.getDate("create_date");

     System.out.println(id+""+real_name+" "+create_date);

             

}

//executeUpdate执行insertupdate,delete

//stmt.executeUpdate("");

 

db.properties(属性文件）

driverClass =oracle.jdbc.OracleDriver

url =jdbc:oracle:thin:@192.168.0.200:1521:tarena

user = jsd1303_1

password = jsd1303_1

 

 

     Propertiespros = new Properties();

      //加载属性文件

     InputStreamis=DBUtils.class.getClassLoader().getResourceAsStream(

                       "com/tarena/jdbctts/po/db.properties");  

     pros.load(is);

     driverClass=pros.getProperty("driverClass");

     url=pros.getProperty("url");

     user=pros.getProperty("user");

     password=pros.getProperty("password");

 

 ============================================================

Class.forName(“com.mysql.jdbc.Driver”);

Stringurl=”jdbc:mysql://127.0.0.1:3306/test”;

String user=”root”;

String pwd=”root”;

 

create table 新表名 as select * from 旧表名;

 

jdbc里面自动提交事物，hibernate是手动提交事物

 

事物的特性acid

    原子性(atomic)

    一致性(consistent)

    隔离性(insulation)

    永久性(duration)

 

conn.setAutoCommit(false);//开启事物

conn.commit();//提交事物

conn.rollback();//回滚事物

 

jdbc批处理(注意：如果使用批处理的话，一定要指定范围，不然容易内存泄露/溢出)

1 连接conn=getconnection();

2 sql语句Stringsql="insert into slf_service_detail" +

              "(id,serviceId,host,osusername,pid,loginTime,logoutTime,duration,"+

              "cost)vaules(service_detail_seq.nextval,?,?,?,?,?,?,?,?)";

3 控制事物conn.setAutoCommit(false);

4 获得stmt  stmt=con.prepareStatement(");

5 添加参数for(ServiceLiuserviceliu:list){

            stmt.setInt(1,serviceliu.getServiceId());

            stmt.setString(2,serviceliu.getHost());

           stmt.setString(3,serviceliu.getOsusername());

            ......

           }

6 添加sql语句stmt.addBatch();

            int batchSize=1000;

            int count=0;

            if(count++>=batchSize){

            stmt.executeBatch();

            count=0;

            }

7 执行批处理stmt.executeBatch();

             stmt.close();

             conn.commit();

//获得Connection对象所消耗的资源会占到整个jdbc操作的85%以上，批处理除外，尽量减少获得Connection次数

/**尽量多次重复使用当前线程的内的connection对象

* 把当前的线程threadLocal跟Connection对象绑定在一次起

* 每次对数据库操作的时候，使用jdbc，获得Connection对象

* 先从当前threadLocal中获得，没有的话才调用getConnection

*/

private staticThreadLocal<Connection> threadLocal=new ThreadLocal<Connection>();

     publicstatic Connection getConnection2() throws SQLException{

         //threadLocal.get():从当前线程中拿到connection对象

         Connectionconnection=threadLocal.get();

         if(connection==null){//当前线程没有connection对象

              //获得新的connection对象

              connection=getConnection();

              //把该connection对象放入当前threadLocal当中

              threadLocal.set(connection);           

         }

     }

//封装事物

public void beginTx(){

     //把jdbc提交事物的方式设置为手动提交

     try{

         Connectionconnection=getConnection2();

         if(connection!=null){

              connection.setAutoCommit(false);

         }

     }catch (SQLException e) {           

         e.printStackTrace();

     }        

}

//提交事物

public void commitTx(){

     try{

         Connectionconnection=getConnection2();

         connection.commit();

     }catch (SQLException e) {           

         e.printStackTrace();

     }

}

//回滚事物

public void rollbackTx(){

     try{

         Connectionconnection=getConnection2();

         connection.rollback();

     }catch (SQLException e) {           

         e.printStackTrace();

     }

}

//关闭connection

public void closeConnection(){

     Connectionconnection=threadLocal.get();

     if(connection!=null){

         try{

              connection.close();

              //把当前threadLoacal中的connection设为null

              threadLocal.set(null);

         }catch (SQLException e) {               

              e.printStackTrace();

         }

     }

}

 

分页查询

select id,uname,pwd,from

(select id,uname,pwd,rownum rfrom service where rownum <110)

作分页

每页放多少数据，总的页数，从第n页开始显示

总的页数＝当前表中所有的数据/每页放的数据的条数

start=rowsPerPage*(page-1)+1;

end=start+ rowsPerPage