培训第四天-----jdbc连接oracle

• oracle链接数据库并向tableone插入中一条数据

package com.zjw.db;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class mydbbase {

    private  Connection conn;

    private  String oracleUrl="jdbc:oracle:thin:@localhost:1521:orcl";

    //private String mysqlUrl="jdbc:mysql://localhost:3306//mysql";

    private  String username = "u1";

    private  String password = "Admin123";

    public mydbbase(){

        try {

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

            DriverManager.setLoginTimeout(0);

            conn = DriverManager.getConnection(oracleUrl,username,password);

            Statement stmt =  conn.createStatement();

            String sql = "insert into tableone(id,name,sex,age) values(1,'jiewei','男','23') ";

            stmt.executeUpdate(sql);

            conn.commit();

            stmt.close();

            conn.close();

        } catch (ClassNotFoundException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    public static void main(String[] args) {

        mydbbase mb = new mydbbase();

    }

}

• 查询数据库中tableone的数据

package com.zjw.db;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class mydbbase {

    private  Connection conn;

    private  String oracleUrl="jdbc:oracle:thin:@localhost:1521:orcl";

    //private String mysqlUrl="jdbc:mysql://localhost:3306//mysql";

    private  String username = "u1";

    private  String password = "Admin123";

    public mydbbase(){

        try {

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

            DriverManager.setLoginTimeout(0);

            conn = DriverManager.getConnection(oracleUrl,username,password);

            Statement stmt =  conn.createStatement();

            //插入语句

            //String sql = "insert into tableone(id,name,sex,age) values(1,'jiewei','男','23') ";

            //String sql = "delete from tableone where id=1";

            //stmt.executeUpdate(sql);

            //查询语句

            String sqlSelect = "select * from tableone";

            ResultSet rs = stmt.executeQuery(sqlSelect);

            while(rs.next()){

                System.out.print(rs.getInt("id")+" ");

                System.out.print(rs.getString("name")+" ");

                System.out.print(rs.getString("sex")+" ");

                System.out.print(rs.getString("age")+" ");

            }

            conn.commit();

            stmt.close();

            conn.close();

        } catch (ClassNotFoundException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    public static void main(String[] args) {

        mydbbase mb = new mydbbase();

    }

}

• 用 preparedStatement来进行增删改查

  //用preparedStatement来进行增删改查
  
      public static boolean auth(String name,String password){
  
          conn = getConnection();
  
          try {
  
              PreparedStatement pstmt = conn.prepareStatement("select * from tableone " + "where name=? and password = ?");
  
              pstmt.setString(1, name);
  
              pstmt.setString(2, password);
  
              ResultSet rs = pstmt.executeQuery();
  
              if(rs.next()){
  
                  return true;
  
              }
  
          } catch (SQLException e) {
  
              // TODO Auto-generated catch block
  
              e.printStackTrace();
  
          }
  
          return false;
  
      }

  解决了当对象没有被NEW 的时候，去setid会出现空指针。

  public userTableDaoBean selector(String where) {
  
          PreparedStatement ps;
  
          userTableDaoBean ub = new userTableDaoBean();
  
          try {
  
              String sql = "select * from usertable where id=?";
  
              ps = conn.prepareStatement(sql);
  
              ps.setInt(1, Integer.parseInt(where));
  
              ResultSet rs = ps.executeQuery();
  
              if(rs.next()){
  
                  ub.setId(rs.getInt("id"));
  
                  ub.setName(rs.getString("name"));
  
                  ub.setSex(rs.getString("sex"));
  
                  ub.setAge(rs.getString("age"));
  
              }
  
              rs.close();
  
              ps.close();
  
          } catch (SQLException e) {
  
              // TODO Auto-generated catch block
  
              e.printStackTrace();
  
          }
  
          return ub;
  
      }
  
  像这个方法，new userTableDaoBean()如果写成null,容易出现空指针。