Oracle学习—数据库连接

数据库的几个连接语句

1.Mysql数据库

StringDriver="com.mysql.jdbc.Driver";   

String URL="jdbc:mysql://localhost:3306/db_name";[W用1] 

2.SqlServer数据库

StringDriver="com.microsoft.sqlserver.jdbc.SQLServerDriver";   

String URL="jdbc:sqlserver://localhost:1433;DatabaseName=db_name";[W用2] 

 

常用sql语句

1.插入语句

insertinto tb_user(uname,upass)values('3333',123);

2.查询语句

select* from tb_user;

select uname,upassfrom tb_user;

3.删除语句

deletefrom tb_user whereuid=1;

truncatetable tb_user;

drop table tb_user;

4.更新语句

update tb_userset uname='战神七'where uid=2;

update tb_userset uname='痛苦女王',upass=123where uid=1;

update tb_userset uname='kusy';

 

 

 

数据库的连接（终极版）

连接数据库的工具类（SqlServer）

packagecom.softeem.db;

importjava.sql.Connection;

importjava.sql.DriverManager;

importjava.sql.PreparedStatement;

importjava.sql.ResultSet;

importjava.sql.SQLException;

 

publicclassDBConnection{

    /**

     *连接数据库的工具类

     */

    privatestaticfinalStringdriver="com.microsoft.sqlserver.jdbc.SQLServerDriver";

    privatestaticfinalStringurl="jdbc:sqlserver://localhost:1433;DatabaseName=test";

    privatestaticfinalStringuser="sa";

    privatestaticfinalStringpwd="123456";

    privatestaticConnectionconn=null;

    static{

       try{

          Class.forName(driver);

       }catch(ClassNotFoundExceptione){

          e.printStackTrace();

       }

    }

    publicstaticConnectiongetConn(){

       if(conn==null){

          try{

             conn=DriverManager.getConnection(url,user,pwd);

          }catch(SQLExceptione){

             e.printStackTrace();

          }

       }

       returnconn;

    }

    publicstaticvoidgetClose(Connectionconn,ResultSetrs,

          PreparedStatementps){

       try{

          if(conn!=null){

             conn.close();

          }

          if(rs!=null){

             rs.close();

          }

          if(ps!=null){

             ps.close();

          }

       }catch(SQLExceptione){

          e.printStackTrace();

       }

    }

}

User对象类（赋值获值）

packagecom.softeem.dto;

publicclassUser{

    /*

     * 一个DTO对应的是一张数据库中的表，字段的数目和类型是和数据库的这张表示相对应的添加一条数据，就是添加一个DTO对象

     */

    privateintuid;

    privateStringuname;

    privateStringupass;

    publicUser(){

       super();

    }

    publicUser(intuid,Stringuname,Stringupass){

       super();

       this.uid=uid;

       this.uname=uname;

       this.upass=upass;

    }

    publicintgetUid(){

       returnuid;

    }

    publicvoidsetUid(intuid){

       this.uid=uid;

    }

    publicStringgetUname(){

       returnuname;

    }

    publicvoidsetUname(Stringuname){

       this.uname=uname;

    }

    publicStringgetUpass(){

       returnupass;

    }

    publicvoidsetUpass(Stringupass){

       this.upass=upass;

    }

}

UserDao接口

packagecom.softeem.dao;

importjava.util.List;

importcom.softeem.dto.User;

publicinterfaceUserDao{

    /*

     * 接口：所有方法的汇集方法没有方法体

     */

    publicbooleanaddUser(Useruser);

    publicbooleandelUser(intuid);

    publicbooleanupdateUser(Useruser);

    publicList<User>listUser();

    publicUsergetUserById(intuid);

}

实现UserDao接口中的实现类（UserDaoImpl）

packagecom.soffteem.daoimpl;

importcom.softeem.dao.UserDao;

importcom.softeem.db.DBConnection;

importcom.softeem.dto.User;

importjava.sql.Connection;

importjava.sql.PreparedStatement;

importjava.sql.ResultSet;

importjava.sql.SQLException;

importjava.sql.Statement;

importjava.util.ArrayList;

importjava.util.List;

publicclassUserDaoImplimplementsUserDao{

    /*

     * 实现类：实现接口重写接口中的所有方法

     */

    privateConnectionconn;

    privateResultSetrs;

    privatePreparedStatementps;

    privateStatementst;

    // 添加数据

    publicbooleanaddUser(Useruser){

       booleanflag=false;

       try{

          conn=DBConnection.getConn();

          Stringsql="insert into tb_user(uname,upass) values(?,?)[W用1] ";

          ps=conn.prepareStatement(sql);//获取预处理命令

          ps.setString(1,user.getUname());//为问号赋值

          ps.setString(2,user.getUpass());

          inti=ps.executeUpdate();

          if(i>0){

             System.out.println("添加成功");

             returntrue;

          }

       }catch(SQLExceptione){

          e.printStackTrace();

       }finally{

          DBConnection.getClose(conn,rs,ps);

       }

       returnflag;

    }

    // 删除数据

    publicbooleandelUser(intuid){

       booleanflag=false;

       try{

          conn=DBConnection.getConn();

          Stringsql="delete from tb_user where uid=?[W用2] ";

          ps=conn.prepareStatement(sql);

          ps.setInt(1,uid);

          booleanb=!ps.execute();

          if(b){

             System.out.println("删除成功！");

             returntrue;

          }

       }catch(SQLExceptione){

          e.printStackTrace();

       }finally{

          DBConnection.getClose(conn,rs,ps);

       }

       returnflag;

    }

    // 根据id查询

    publicUsergetUserById(intuid){

       Useruser=null;

       try{

          conn=DBConnection.getConn();

          Stringsql="select * from tb_user where uid=?[W用3] ";

          ps=conn.prepareStatement(sql);

          ps.setInt(1,uid);

          rs=ps.executeQuery();

          while(rs.next()){

             intid=rs.getInt("uid");

             Stringname=rs.getString("uname");

             Stringpass=rs.getString("upass");

             user=newUser(id,name,pass);

          }

       }catch(SQLExceptione){

          e.printStackTrace();

       }finally{

          DBConnection.getClose(conn,rs,ps);

       }

       returnuser;

    }

    // 查询所有查询表查到的肯定是很多条记录

    publicList<User>listUser(){

       List<User>list=newArrayList<User>();

       try{

          conn=DBConnection.getConn();

          Stringsql="select* from tb_user";

          st=conn.createStatement();

          rs=st.executeQuery(sql);

          while(rs.next()){

             intid=rs.getInt("uid");

             Stringname=rs.getString("uname");

             Stringpass=rs.getString("upass");

             Useruser=newUser(id,name,pass);

             list.add(user);[W用4] 

          }

       }catch(SQLExceptione){

          e.printStackTrace();

       }finally{

          DBConnection.getClose(conn,rs,ps);

       }

       returnlist;

    }

    publicbooleanupdateUser(Useruser){

       booleanflag=false;

       try{

          conn=DBConnection.getConn();

          Stringsql="update tb_user set uname=?,upass=? where uid=?[W用5] ";

          ps=conn.prepareStatement(sql);

          ps.setString(1,user.getUname());

          ps.setString(2,user.getUpass());

          ps.setInt(3,user.getUid());

          booleanb=!ps.execute();

          if(b){

             System.out.println("更新数据成功！");

             returntrue;

          }

       }catch(SQLExceptione){

          e.printStackTrace();

       }finally{

          DBConnection.getClose(conn,rs,ps);

       }

       returnflag;

    }

}

测试类 Test

packagecom.softeem.test;

importjava.util.Iterator;

importjava.util.List;

importcom.soffteem.daoimpl.UserDaoImpl;

importcom.softeem.dao.UserDao;

importcom.softeem.dto.User;

publicclassTest{

    publicstaticvoidmain(String[]args){

       // 添加数据

       // Useruser=new User(0,"KK","JJ");

        // //接口回调

       // UserDaouserdao=new UserDaoImpl();

       // booleanb=userdao.addUser(user);

       //System.out.println("添加成功："+b);

 

       // 删除数据

       // UserDaou2 = new UserDaoImpl();

       // booleanb = u2.delUser(1);

       //System.out.println("删除成功：" + b);

 

       // 根据id查询数据

       // UserDaou3 = new UserDaoImpl();

       // Useruser = u3.getUserById(2);

       //System.out.println(user.getUid() + " " + user.getUname() + ""

       // +user.getUpass());

 

       // 查询所有数据

       // UserDaou4 = new UserDaoImpl();

       //List<User> list = u4.listUser();// 对象调用方法查询用集合接受因为有很多条记录

       //Iterator<User> user = list.iterator();//对集合的数据进行迭代

       // while(user.hasNext()) {

       // User u= user.next();// 一个一个的循环对象

       //System.out.println(u.getUid() + " " + u.getUname() + " "

       // +u.getUpass());

       // }[W用6] 

 

       // 更新数据

       UserDaou5=newUserDaoImpl();

       Useruser=newUser(7,"战神七","专业一换一");

       booleanb=u5.updateUser(user);

       System.out.println("更新数据成功！"+b);

    }

}