---角色类型表RoleType IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[RoleTypeList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [RoleTypeList] GO CREATE TABLE RoleTypeList ( RoleType INT IDENTITY(1,1) PRIMARY KEY, RoleTypeName NVARCHAR(50) NOT NULL, RoleTypeDescribe NTEXT NULL ) GO insert into RoleTypeList(RoleTypeName,RoleTypeDescribe) values(N'系统管理',N'超级权限'); insert into RoleTypeList(RoleTypeName,RoleTypeDescribe) values(N'管理层',N'高级权限'); insert into RoleTypeList(RoleTypeName,RoleTypeDescribe) values(N'普通员工',N'一般用户'); go select * from RoleTypeList go IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_RoleTypeListAll') DROP PROCEDURE dbo.proc_Select_RoleTypeListAll GO CREATE PROCEDURE dbo.proc_Select_RoleTypeListAll AS SELECT * FROM dbo.RoleTypeList GO exec proc_Select_RoleTypeListAll go IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_Operation') DROP PROCEDURE dbo.proc_Insert_Operation GO CREATE PROCEDURE dbo.proc_Insert_Operation ( @RoleTypeName NVarChar(50), @RoleTypeDescribe NVarChar(200) ) AS INSERT INTO dbo.RoleTypeList ( RoleTypeName , RoleTypeDescribe ) VALUES ( @RoleTypeName , @RoleTypeDescribe ) GO IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_RoleTypeListOutput') DROP PROCEDURE dbo.proc_Insert_RoleTypeListOutput GO CREATE PROCEDURE dbo.proc_Insert_RoleTypeListOutput ( @RoleTypeName NVarChar(50), @RoleTypeDescribe NVarChar(200), @RoleType int output ) AS INSERT INTO dbo.RoleTypeList ( RoleTypeName , RoleTypeDescribe ) VALUES ( @RoleTypeName , @RoleTypeDescribe ) select @RoleType=@@IDENTITY GO IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_RoleTypeList') DROP PROCEDURE dbo.proc_Update_RoleTypeList GO CREATE PROCEDURE dbo.proc_Update_RoleTypeList ( @RoleTypeName NVarChar(50), @RoleTypeDescribe NVarChar(200), @RoleType int ) AS UPDATE dbo.RoleTypeList SET [RoleTypeName]=@RoleTypeName , [RoleTypeDescribe]=@RoleTypeDescribe where RoleType =@RoleType GO IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_RoleTypeList') DROP PROCEDURE dbo.proc_Delete_RoleTypeList GO CREATE PROCEDURE dbo.proc_Delete_RoleTypeList ( @RoleType Int ) as DELETE dbo.RoleTypeList WHERE RoleType = @RoleType GO IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_RoleTypeList') DROP PROCEDURE dbo.proc_Select_RoleTypeList GO CREATE PROCEDURE dbo.proc_Select_RoleTypeList ( @RoleType Int ) AS SELECT * FROM dbo.RoleTypeList WHERE RoleType = @RoleType GO
DAL:
/* * 版权所有 2021 涂聚文有限公司 * 许可信息查看: * 描述: * IDE:IntelliJ IDEA 2021.2.3 * 历史版本: JDK 14.02 * 2021-12-12 创建者 geovindu * 2021-12-15 添加 Lambda * 2021-12-15 修改:date * 接口类 mssql-jdbc-9.4.1.jre8.jar. * 数据库:MSSQL Server 2019 * 2021-12-15 修改者:Geovin Du * 生成API帮助文档的指令: *javadoc - -encoding Utf-8 -d apidoc DuMsSQLConn.java *https://www.microsoft.com/en-us/software-download/windows10 *https://github.com/PaddlePaddle/PaddleOCR *https://docs.microsoft.com/es-es/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15 *https://github.com/microsoft/mssql-jdbc/blob/main/README.md * */ import Geovin.Model.OutValue; import Geovin.Model.RoleTypeList; import java.io.Reader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import java.sql.*; import java.util.ArrayList; //import com.microsoft.*; /** * @author geovindu * @version 1.0 * * */ public class DuMsSqlConn { //数据库地址 //1 可以用 //String url = "jdbc:sqlserver://127.0.0.1:1433;DataBaseName=geovindu"; //GEOVINDU //localhost 127.0.0.1:1433 //2 String url = "jdbc:sqlserver://GEOVINDU;DataBaseName=geovindu"; //1 String connectionUrl = "jdbc:sqlserver://GEOVINDU;databaseName=geovindu;user=sa;password=geovindu"; // String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //com.mysql.cj.jdbc.Driver //com.mysql.jdbc.Driver "com.microsoft.sqlserver.jdbc.SQLServerDriver // String userName = "sa"; // String password = "geovindu"; Connection con; Statement stmt = null; //proc PreparedStatement pstmt=null; //SQl CallableStatement cstmt=null; //PRoc /** * 连接 * * */ public Connection getConnection() { try { Class.forName(driverName); //System.out.println("Ms SQL 数据库驱动加载成功"); } catch (ClassNotFoundException e) { e.printStackTrace(); } try { con = DriverManager.getConnection(connectionUrl); //2 第二种方法 //con = DriverManager.getConnection(url,userName,password); // System.out.println("Ms SQL数据库连接成功"); //2 //stmt = con.createStatement(); // con.getMetaData() /* String sql; sql = "SELECT RoleType,RoleTypeName,RoleTypeDescribe FROM RoleTypeList"; pstmt=con.prepareStatement(sql); //2 // ResultSet rs = stmt.executeQuery(sql); ResultSet rs =pstmt.executeQuery(); // 展开结果集数据库 while(rs.next()){ // 通过字段检索 int typid = rs.getInt("RoleType"); String rolename = rs.getString("RoleTypeName"); String roledec = rs.getString("RoleTypeDescribe"); // 输出数据 System.out.print("角色ID: " + typid); System.out.print(" , 角色名字: " + rolename); System.out.print(" , 角色描述: " + roledec); System.out.print("\n"); } // 完成后关闭 rs.close(); stmt.close(); con.close(); */ } catch (SQLException e) { e.printStackTrace(); } return con; } /** *SQL 语句查询所有 * @param * @return 返回集合 * */ public ArrayList<RoleTypeList> selectSqlAll() { ArrayList<RoleTypeList> alist=new ArrayList<RoleTypeList>(); RoleTypeList roleTypeList=null; ResultSet resultSet=null; try { Connection connection = getConnection(); String sql; sql = "SELECT RoleType,RoleTypeName,RoleTypeDescribe FROM RoleTypeList"; pstmt = con.prepareStatement(sql); //2 // rs = stmt.executeQuery(sql); resultSet = pstmt.executeQuery(); // 展开结果集数据库 while (resultSet.next()) { // 通过字段检索 roleTypeList=new RoleTypeList(); roleTypeList.setRoleType(resultSet.getInt("RoleType")); roleTypeList.setRoleTypeName(resultSet.getString("RoleTypeName")); roleTypeList.setRoleTypeDescribe(resultSet.getString("RoleTypeDescribe")); alist.add(roleTypeList); } } catch (SQLException exception) { exception.printStackTrace(); } return alist; } /** *存储过程 查询所有 * @param * @return 返回集合 * */ public ArrayList<RoleTypeList> selectProclAll() { ArrayList<RoleTypeList> alist=new ArrayList<RoleTypeList>(); RoleTypeList roleTypeList=null; ResultSet resultSet=null; try { Connection connection = getConnection(); String sql; sql = "proc_Select_RoleTypeListAll"; //pstmt = con.prepareStatement(sql); cstmt=con.prepareCall(sql); //2 // resultSet = stmt.executeQuery(sql); // resultSet = pstmt.executeQuery(); resultSet = cstmt.executeQuery(); // 展开结果集数据库 while (resultSet.next()) { // 通过字段检索a roleTypeList=new RoleTypeList(); roleTypeList.setRoleType(resultSet.getInt("RoleType")); roleTypeList.setRoleTypeName(resultSet.getString("RoleTypeName")); roleTypeList.setRoleTypeDescribe(resultSet.getString("RoleTypeDescribe")); alist.add(roleTypeList); } } catch (SQLException exception) { exception.printStackTrace(); } return alist; } /** *添加 * @param roleTypeList * @return * */ public int AddSQL(RoleTypeList roleTypeList) { int isok=0; try { Connection connection = getConnection(); String sql; sql = "INSERT INTO dbo.RoleTypeList(RoleTypeName ,RoleTypeDescribe) VALUES(? ,?)"; pstmt = con.prepareStatement(sql); // pstmt.setInt(1,roleTypeList.getRoleType()); pstmt.setString(1,roleTypeList.getRoleTypeName()); pstmt.setString(2, roleTypeList.getRoleTypeDescribe()); //2 // rs = stmt.executeQuery(sql); isok = pstmt.executeUpdate(); } catch (SQLException exception) { exception.printStackTrace(); } return isok; } /** *添加 存储过程 * @param roleTypeList * @return * */ public int AddProc(RoleTypeList roleTypeList) { //SQLServerCallableStatement int isok=0; try { Connection connection = getConnection(); String sql; sql = "{call proc_Insert_Operation(? ,?)}"; //pstmt = con.prepareStatement(sql); cstmt=con.prepareCall(sql); // pstmt.setInt(1,roleTypeList.getRoleType()); cstmt.setString(1,roleTypeList.getRoleTypeName()); cstmt.setString(2, roleTypeList.getRoleTypeDescribe()); //2 // rs = stmt.executeQuery(sql); isok = cstmt.executeUpdate(); } catch (SQLException exception) { exception.printStackTrace(); } return isok; } /** *添加 返回 * @param roleTypeList * @param outValue 返回值 不成功 * */ public int AddSQLOut(RoleTypeList roleTypeList, OutValue outValue) { int isok=0; try { Connection connection = getConnection(); String sql; sql = "INSERT INTO dbo.RoleTypeList(RoleTypeName ,RoleTypeDescribe) VALUES(? ,?)"; pstmt = con.prepareStatement(sql); // pstmt.setInt(1,roleTypeList.getRoleType()); pstmt.setString(1,roleTypeList.getRoleTypeName()); pstmt.setString(2, roleTypeList.getRoleTypeDescribe()); //2 // rs = stmt.executeQuery(sql); isok = pstmt.executeUpdate(); String sql2="select @@IDENTITY"; PreparedStatement preparedStatement=null; preparedStatement=con.prepareStatement(sql2); //cstmt= connection.prepareCall(sql2); ResultSet resultSet=preparedStatement.executeQuery(); //cstmt.executeQuery(); outValue.setIntValue(resultSet.getInt(1)); } catch (SQLException exception) { exception.printStackTrace(); } return isok; } /** *添加反回值 * @param roleTypeList * @param outValue * @return * */ public int AddProcOut(RoleTypeList roleTypeList, OutValue outValue) { //SQLServerCallableStatement int isok=0; try { Connection connection = getConnection(); String sql; sql = "{call proc_Insert_RoleTypeListOutput(? ,?,?)}"; //pstmt = con.prepareStatement(sql); cstmt=con.prepareCall(sql); // pstmt.setInt(1,roleTypeList.getRoleType()); cstmt.setString(1,roleTypeList.getRoleTypeName()); cstmt.setString(2, roleTypeList.getRoleTypeDescribe()); cstmt.registerOutParameter(3,Types.INTEGER); //2 // //cstmt.execute();// isok=cstmt.executeUpdate(); // System.out.println(cstmt.getInt(3)); outValue.setIntValue(cstmt.getInt(3)); // isok =1; } catch (SQLException exception) { exception.printStackTrace(); } return isok; } }