ITdaan
搜索本文相关内容

总结基于mysql，oracle的jdbc对数据库的操作（增删查改，批量操作，事务）

本文转载自  shenbushen  查看原文  2016/04/08  2    数据/ 操作/ oracle/ 总结/ jdbc/ mysql/ sql           

jdbc对数据库的操作对于java web来说是非常重要的，当然现在有很多操作数据库的框架供我们操作，但是究其根本，都是封装了jdbc的：所以我把它总结总结我的使用方法：

主要的几个对象：    PreparedStatement ps = conn.prepareStatement(sql);             查询使用：ResultSet rs = ps.executeQuery();    增删改使用：ps.executeUpdate()


我一般使用一个静态类（也可不静态）,来封装数据库的直接操作：

public class sqlDao {

 private static final String Driver = "com.mysql.jdbc.Driver";                    //驱动
 private static final String Url = "jdbc:mysql://localhost:3309/sshtext?useUnicode=true&characterEncoding=gbk";     //为了应对乱码加了后面的编码
 pvate static final String Username = "root";                                                                                                         //账号
 private static final String Password = "123456";                                                                                                //密码


private Connection conn = null;

public void connect() {                            //连接
 try {
  Class.forName(Driver);
  conn = (Connection) DriverManager.getConnection(Url, Username, Password);
  if (conn.isClosed()) {
   System.out.println("connect  not success");
   conn = null;
  }
 } catch (ClassNotFoundException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
 } catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
 }
}

public void closeConn() {                              //关闭
 try {
  if (conn != null && !conn.isClosed()) {
   conn.close();
  }
 } catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
 }
}


///////////////////////////////////////华丽分界线/////////////////////////////////////////

增删查找：


///查找：

//类似登录，检测，查重，列出数据等等业务操作


 public String Login(AccountMessage account) {               //传入一个实体类
  boolean flag = false;
  // TODO MODIFY
  String temp = "";
  connect();
  try {
   String sql = "SELECT * FROM tb_user WHERE user_id = ? AND password = ?;";                   //定义sql语句，使用占位符
   PreparedStatement ps = conn.prepareStatement(sql);
   ps.setString(1, account.getUsername());                                                                                 //设置占位符的数据
   ps.setString(2, account.getPassword());
   ResultSet rs = ps.executeQuery();                                                                                         //执行查询
   if (rs != null && rs.next()) {                                                                                                      //若查询非空，且有数据
    flag = true;
    temp = rs.getString(2);                                                                                                           //通过rs.getString(index)的方式获取查询行列的数据
    temp = temp + ":";
    temp = temp + rs.getString(4);
    temp = temp + ":";
    temp = temp + rs.getString(5);
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   closeConn();
  }
  return temp;
 }



///增加：主要是注册啊,写进记录等

public boolean tieNeiRong(TieziMessage tiezi) {
  boolean flag = false;
  connect();
  String sql = "INSERT INTO tb_tie(tie_id,username,leibie, content, lujin, time) VALUES(?,?,?,?,?,?);";
  try {
   PreparedStatement ps = conn.prepareStatement(sql);
   ps.setString(1, tiezi.gettie_Id());
   ps.setString(2, tiezi.getUsername());
   ps.setString(3, tiezi.getleibie());
   ps.setString(4, tiezi.gettie_content());
   ps.setString(5, tiezi.getlujin());
   ps.setString(6, tiezi.gettime());
   if (ps.executeUpdate() == 1) {
    flag = true;
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }

  return flag;
 }


////删除

public boolean Delete_tiezi(int tie_id) {
  connect();
  boolean flag = false;
   try {
    String sql ="delete  from tb_tie where tie_id ="+tie_id+";";                        //直接拼字符串
    PreparedStatement ps = conn.prepareStatement(sql);
  
    //ps.setInt(1, tie_id);                                                                                 //占位符
    
    if (ps.executeUpdate(sql)== 1) {
     flag = true;
    }
   } catch (SQLException e) {
    e.printStackTrace();
   } finally {
    closeConn();
   }
  
  
  return flag;
 }



//修改

 public boolean UpdateMsg(String username, String nickname, String sex) {
  boolean flag = false;
  connect();
  try {
   String sql = "UPDATE tb_user SET username = ?, sex = ? WHERE user_id = ?";
   PreparedStatement ps = conn.prepareStatement(sql);
   ps.setString(1, nickname);
   ps.setString(2, sex);
   ps.setString(3, username);
   if (ps.executeUpdate() == 1) {
    flag = true;
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   closeConn();
  }

  return flag;
 }



////////////会了简单增删改,还得会批量增删查改,这涉及到数据库事务的管理了，再批量增删查改时，如果是一条一条提交，然后在中间突然出现错误，后面的没有提交，这就会造成部分数据增删查改，部分没有执行，这肯定是不行的////////////////////

//批量删除的例子：前台传来一条用户账号组成的字符串,我们把他转换成整形数组，然后整形数组传进来


public boolean deletebyuser_ids(int ids[]) throws SQLException     //出错回滚
{
 connect();
 
 try {
  conn.setAutoCommit(false);    //设置手动提交
  String sql="delete from user_role where user_id=?;";
  PreparedStatement ps =  (PreparedStatement) conn.prepareStatement(sql);
 for(int i=0;i<ids.length;i++)
 { 
  
   ps.setInt(1, ids[i]);
   ps.addBatch();
 }

//ps.addBatch("sql语句");             //添加静态的sql语句

  ps.executeBatch();
   try {
    conn.commit();     //手动 提交
   } catch (SQLException e1) {
    conn.rollback();         //出错回滚
    return false;
   }
  return true;
 
 }
 catch (SQLException e) {
  e.printStackTrace();
  return false;
 }
 finally
 {
  conn.setAutoCommit(true);   //最后设为主动提交
 }
 
 
}


//当然也可以使用拼接字符串的方式去执行批量删除

public boolean deleteall(int ids[])             //拼接字符串方式删除
{
 connect();
 String sql="delete from user_role where user_id in";
 try {
  String canshuc=");";
  String canshua="(";
  String canshub="";
  for(int i=0;i<ids.length;i++)
  {
   canshub+=ids[i]+",";
  }
  
  canshub=canshub.substring(0, canshub.length()-1);
  sql=sql+canshua+canshub+canshuc;
  
  PreparedStatement ps =  (PreparedStatement) conn.prepareStatement(sql);
  int a=ps.executeUpdate();
  
 } catch (SQLException e) {
  // TODO Auto-generated catch block
  
  e.printStackTrace();
  return false;
 }
return true;
  
}


}


/////////////华丽分割线////////////


使用例子：仅仅在需要用到的地方填上


sqlDao sdo=new sqlDao();              //创建对象
   boolean a=sdo.deletebyuser_id(user.getId());        //调用函数
   
   sdo.closeConn();                             //关闭连接




MYSQL总结完毕.


下面是jdbc对Oracle数据库的操作：需要oracle的jdbc操作包。

首先是连接数据库：

 private static final String driverUrl = "oracle.jdbc.driver.OracleDriver";  
  
    private static final String url = "jdbc:oracle:thin:@127.0.0.1:1521:myfirst";  
      
    private static final String username = "XIAOHUA";  
      
    private static final String password = "123456";  
      
    public static Connection getConnection(){  
        Connection connection = null;  
        try {  
            Class.forName(driverUrl);  
            connection = DriverManager.getConnection(url, username, password);  
            
        } catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
        return connection;  
    }


然后是：基本的增删查改：

新增：

    //新增 
    public static void main(String[] args) {  
        Connection connection = Helsd.getConnection();  
        String sql = "INSERT INTO T_ADD(RESOURCEID,NAME) VALUES(?,?)";
        try {
         PreparedStatement ps = connection.prepareStatement(sql);
         ps.setString(1, "WENYSSSSssssssss");
         ps.setString(2, "B");
         if (ps.executeUpdate() == 1) {
         System.out.println("新增成功："+connection);  
         }
    }  catch (Exception e) {
System.out.print(e.getMessage());
}
       finally
       {
       try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
       }
    }


删除：

   public static void main(String[] args) {  
        Connection connection = Helsd.getConnection();  
        try {

       
         String sqls = "delete from T_ADD where NAME=?";
         PreparedStatement pss = connection.prepareStatement(sqls);
         pss.setString(1, "B");
         pss.execute();
    }  catch (Exception e) {
System.out.print(e.getMessage());
}
       finally
       {
       try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
       }
    }

查询：

  public static void main(String[] args) {  
        Connection connection = Helsd.getConnection();  
        try {

       
         String sqls = "select * from T_ADD where NAME=?";
         PreparedStatement pss = connection.prepareStatement(sqls);
         pss.setString(1, "2");
        ResultSet rs= pss.executeQuery();
        while(rs.next())
            {
        System.out.print(rs.getString(1));
        System.out.print(rs.getString(2));
            }
    }  catch (Exception e) {
System.out.print(e.getMessage());
}
       finally
       {
       try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
       }
    }

改：

  public static void main(String[] args) {  
        Connection connection = Helsd.getConnection();  
        try {

       
         String sqls = "update T_ADD set NAME =? WHERE RESOURCEID=?";
         PreparedStatement pss = connection.prepareStatement(sqls);
         pss.setString(1, "wenyan");
         pss.setString(2,"w");
       pss.execute();
    }  catch (Exception e) {
System.out.print(e.getMessage());
}
       finally
       {
       try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
       }
    }


存储过程：

存储过程语句：

第一种：只有IN参数的：

存储过程：

CREATE OR REPLACE 
PROCEDURE INSERTADD(RESOURCES IN VARCHAR2,NAMES IN VARCHAR2) AS
BEGIN
INSERT INTO  T_ADD(RESOURCEID,NAME) VALUES(RESOURCES,NAMES);
END;

jdbc调用:

public static void main(String[] args) {  
        Connection connection = Helsd.getConnection();  
        try {
        CallableStatement cs = connection.prepareCall("{call insertadd(?,?)}");
         
            cs.setString(1, "资源1");
            cs.setString(2,"名称1");
             cs.execute(); //执行查询操作，并获取结果集
    }  catch (Exception e) {
System.out.print(e.getMessage());
}
       finally
       {
       try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
       }
    }



第二种：两个参数，第一个参数是传进来的RESOURCEID,第二个参数是输出，输出的是单个值

CREATE OR REPLACE 
PROCEDURE getname(RESOURCEIDS IN VARCHAR2, NAMES OUT VARCHAR2)
AS
BEGIN
select NAME INTO NAMES from T_ADD where RESOURCEID=RESOURCEIDS;
END;


代码调用：

 public static void main(String[] args) {  
        Connection connection = Helsd.getConnection();  
        try {
        CallableStatement cs = connection.prepareCall("{call getname(?,?)}");
         
            cs.setString(1, "w");                           
            cs.registerOutParameter(2,java.sql.Types.VARCHAR);
             cs.execute(); //执行查询操作，并获取结果集
       String str= cs.getString(2);
       System.out.print(str);
    }  catch (Exception e) {
System.out.print(e.getMessage());
}
       finally
       {
       try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
       }
    }

第三种：两种参数，输出的是表：通过先定义包，定义好类型CURSOR，程序拿到这个cursor装换成结果集拿出数据：

建包：

CREATE OR REPLACE 
PACKAGE AA AS 
TYPE TEST_CURSOR IS REF CURSOR; 
end;
建存储过程：

CREATE OR REPLACE 
PROCEDURE GETLIST(p_name IN VARCHAR2,P_CUSER OUT AA.TEST_CURSOR) AS
BEGIN
OPEN P_CUSER FOR
SELECT * FROM T_ADD WHERE NAME=p_name;
END;

jdbc调用然后拿数据：

    public static void main(String[] args) {  
        Connection connection = Helsd.getConnection();  
        try {
        CallableStatement cs = connection.prepareCall("{call getlist(?,?)}");
         
            cs.setString(1, "3");
            cs.registerOutParameter(2,OracleTypes.CURSOR);
             cs.execute(); //执行查询操作，并获取结果集
             ResultSet rs = (ResultSet) cs.getObject(2);  
             // 获取列名及类型  
             int colunmCount = rs.getMetaData().getColumnCount();  
             String[] colNameArr = new String[colunmCount];  
             String[] colTypeArr = new String[colunmCount];  
             for (int i = 0; i < colunmCount; i++) {  
                 colNameArr[i] = rs.getMetaData().getColumnName(i + 1);  
                 colTypeArr[i] = rs.getMetaData().getColumnTypeName(i + 1);  
                 System.out.print(colNameArr[i] + "(" + colTypeArr[i] + ")"  
                         + " | ");  
             }  
             System.out.println();  
             while (rs.next()) {  
                 StringBuffer sb = new StringBuffer();  
                 for (int i = 0; i < colunmCount; i++) {  
                     sb.append(rs.getString(i + 1) + " | ");  
                 }  
                 System.out.println(sb);  
             }  
             System.out.println("------- Test Proc Out is ResultSet end. ");  
    }  catch (Exception e) {
System.out.print(e.getMessage());
}
       finally
       {
       try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
       }
    }

第四种：输入输出IN OUT  输入输出都是它：

存储过程：

CREATE OR REPLACE 
PROCEDURE testinout(p_name IN OUT VARCHAR2) AS
BEGIN
SELECT count(RESOURCEID) INTO p_name from T_ADD WHERE NAME=p_name;
END;

代码：

   // 测试Oracle连接是否成功  
    public static void main(String[] args) {  
        Connection connection = Helsd.getConnection();  
        try {
        CallableStatement cs = connection.prepareCall("{call testinout(?)}");
         
            cs.setString(1, "3");
            cs.registerOutParameter(1,java.sql.Types.VARCHAR);
             cs.execute(); //执行查询操作，并获取结果集
       String str= cs.getString(1);
       
       System.out.print(str);
    }  catch (Exception e) {
System.out.print(e.getMessage());
}
       finally
       {
       try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
       }
    }














智能推荐

注意！

本站转载的文章为个人学习借鉴使用，本站对版权不负任何法律责任。如果侵犯了您的隐私权益，请联系我们删除。



猜您在找
通过jdbc实现对数据库中数据的增删查改操作 Oracle数据库对表基本的操作--增删查改 Java通过JDBC实现对数据库的增删查改（预编译方式） python操作mysql数据库（增删查改） mysql数据库批量操作
智能推荐
 
© 2014-2019 ITdaan.com 粤ICP备14056181号  

赞助商广告