澳门十大赌场最新排名 > 澳门十大赌场 > 2008从入门到精通,使用脚本输出excel文件的方法

原标题:2008从入门到精通,使用脚本输出excel文件的方法

浏览次数:132 时间:2019-07-12

约束

转自:

JDBC的简单介绍

主关键字约束(Primary Key Constraint)

用来指定表中的一列或几列组合的值在表中具有唯一性。建立主键的目的是让外键来引用。


概念

  • JDBC : Java Database Connectivity,java连接数据库,使用Java操作数据库的技术。
  • 本质 : 其实就是sun公司定义的一套操作所有关系型数据库的规则,即接口。
    各个数据库厂商去实现这套接口,提供数据库操作驱动jar包,我们可以使用这套
    接口进行(JDBC)编程,真正执行的代码是驱动jar包的实现类。
Primary Key的创建方式

在创建表时创建Primary Key

CREATE TABLE table1(
    t_id VARCHAR(12) ,
    t_name VARCHAR(20),
    t_phone VARCHAR(20),
    CONSTRAINT t_idss PRIMARY KEY(t_id)
);

对t_id列创建主键,约束名为t_idss。

快速入门

本人使用的是jdk10和mysql-connector-java-5.1.45-bin.jar。

步骤:

  1. 导入驱动jar包
    • 项目根目录创建文件夹libs。
    • 将mysql-connector-java-5.1.45-bin.jar复制到libs目录下
    • 右击libs下,Add as Library将jar包导入项目中
  2. 注册驱动
  3. 获取数据库连接对象(Collection)
  4. 定义sql
  5. 获取执行sql语句的对象 Statement
  6. 执行sql,接受返回结果(ResultSet)
  7. 处理结果
  8. 释放资源

     // 2. 注册驱动
     Class.forName("com.mysql.jdbc.Driver");
     // 3. 获取连接对象Collection
     Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "nrblwbb7");
     // 4. 定义sql
     String sql = "UPDATE STUDENT SET AGE = 23 WHERE NAME = '王智'";
     // 5. 获取执行sql语句的对象 Statement
     Statement statement = conn.createStatement();
     // 6. 执行sql,接受返回结果(ResultSet)
     int result = statement.executeUpdate(sql);
     // 7. 处理结果
     System.out.println(result);
     // 8. 释放资源
     statement.close();
     conn.close();
    
删除Primary Key
ALTER TABLE table1
DROP CONSTRAINT t_idss;

约束名与列名不一致,此处填写约束名

摘要:

详解各个对象

  1. DriverManager:驱动管理对象
  2. Connection:数据库连接对象
  3. Statement:执行sql对象
  4. ResultSet:结果集对象
  5. PreparedStatement :执行sql对象
向已有表中添加Primary Key
ALTER TABLE table1
ADD CONSTRAINT t_idss
PRIMARY KEY(t_id);

下文将分享使用sql脚本输出excel的方法
 
此脚本可以应用于 表或视图生成excel的方法,
若需使用sql脚本输出excel数据,我们可将sql脚本生成视图或临时表,

DriverManager

功能:

  1. 注册驱动:告诉程序该使用哪一个数据库驱动jar

    • 使用的方法:static void registerDriver(Driver driver) :注册与给定的驱动程序 DriverManager 。
    • 写代码使用:Class.forName("com.mysql.jdbc.Driver");
    • 通过查看源码发现:在com.mysql.jdbc.Driver类中存在静态代码块

         static {
                try {
                    java.sql.DriverManager.registerDriver(new Driver());
                } catch (SQLException E) {
                    throw new RuntimeException("Can't register driver!");
                }
         }
      

      注意:mysql5之后的驱动jar包可以省略注册驱动的步骤。

  2. 获取数据库连接

    • 方法:static Connection getConnection(String url, String user, String password)
    • 参数:
      • url:指定连接的路径
        • 语法:jdbc:mysql://ip地址(域名):端口号/数据库名称
        • 例子:jdbc:mysql://localhost:3306/db
        • 细节:如果连接的是本机mysql服务器,并且mysql服务默认端口是3306,则url可以简写为:jdbc:mysql:///数据库名称
        • jdbc是连接数据库的协议,mysql是连接mysql数据库的子协议
      • user:用户名
      • password:密码
添加Primary Key的另一种示例
ALTER TABLE Products
ADD PRIMARY KEY(prod_id);

虽然上述代码运行没问题,查看表格设计也可以看到Primary Key设置成功,但是在删除Primary Key操作时会提示:
消息3728,级别16,状态1,第1 行
'prod_id' 不是约束。
消息3727,级别16,状态0,第1 行
未能删除约束。请参阅前面的错误信息。
原因是添加Primary Key语句中没有用CONSTRAINT指明约束名,系统自动生成了主键名和约束名,要先查看主键名和约束名,删除时填写的也是约束名。
这种情况的正确删除方法

ALTER TABLE Products
DROP CONSTRAINT CK__Products__prod_p__1A14E395;
ALTER TABLE Products
DROP CONSTRAINT PK__Products__56958AB222AA2996;

然后再输出excel

输出excel语法简介:
exec
master..xp_cmdshell 'bcp [数据库名称].[架构名].[表名] out [excel存放位置全路径] -c -q -S"服务器Ip" -U "sql用户名" -P "sql密码"'

---例1:
exec 
master..xp_cmdshell 'bcp test.dbo.tableName out d:test.xls -c -q -S"." -U "sa" -P "erp"'
---将数据库test中tablName输出值d盘test.xls文件
---服务器地址.
---sql账户sa
---sql密码erp
--------------------------------------------
例2: 根据动态文件名输出excel的方法
declare @name varchar(30) ---动态文件名
set @name ='d:test123.xlsx'

exec 
('master..xp_cmdshell ''bcp test.dbo.tableName out ' @name ' -c -q -S"." -U "sa" -P "erp"''') ---拼接操作语句,并采用exec执行

注意事项:

*1 使用脚本输出的excel ,无表头
2 可动态文件名和动态sa账户密码生成相关信息
3 如果输出sql脚本的数据至excel,我们需先将sql脚本生成的数据缓存至表中,然后输出表数据至excel

xp_cmdshell相关权限需打开
*

Collection连接对象

功能:

  1. 获取执行sql的对象
    • Statement createStatement()
    • PreparedStatement prepareStatement(String sql)
  2. 管理事务
    • 开启事务:setAutoCommit(boolean autoCommit) :调用该方法设置参数为false,即开启事务
    • 提交事务:commit()
    • 回滚事务:rollback()
多列组合添加主键约束
CREATE TABLE table1(
    t_id VARCHAR(12),
    s_id VARCHAR(20),
    score FLOAT,
    CONSTRAINT ts_id PRIMARY KEY(t_id,s_id)
);
Statement执行静态sql对象

功能:

  1. 执行sql
    • boolean execute(String sql) :可以执行任意的sql (了解)
    • int executeUpdate(String sql) :执行DML(insert、update、delete)语句、DDL(create,alter、drop)语句
      • 返回值:影响的行数,可以通过这个影响的行数判断DML语句是否执行成功 返回值>0的则执行成功,反之,则失败。
    • ResultSet executeQuery(String sql) :执行DQL(select)语句

插入举例:

 public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try {
            // 1. 加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 2. 获取连接对象
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3?useSSL=true", "root", "nrblwbb7");
            // 3. 创建sql
            String sql = "INSERT INTO ACCOUNT VALUES (NULL,'网址',2000)";
            // 4. 获取执行sql对象
            stmt = conn.createStatement();
            // 5. 执行sql
            int count = stmt.executeUpdate(sql);
            // 6. 处理结果
            System.out.println("影响的行数为:"   count);
            if(count > 0){
                System.out.println("插入成功");
            }else{
                System.out.println("操作失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 7. 释放资源
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
  }

修改举例:

 public static void main(String[] args) {
        /**
         * 由于Connection和Statement实现了AutoCloseable接口,所以可以使用自动关闭
         */
        try(
                // 2.获取Connection对象
                Connection conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/db3?useSSL=true",
                        "root",
                        "nrblwbb7") ;
                // 3. 获取Statement对象
                Statement stmt = conn.createStatement()
                ){
            // 1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 4. 创建sql
            String sql = "UPDATE ACCOUNT SET BALANCE = 1500 WHERE NAME = '网址'";
            // 5. 执行sql
            int count = stmt.executeUpdate(sql);
            // 6. 处理结果
            System.out.println(count);
            if(count > 0){
                System.out.println("修改成功");
            }else{
                System.out.println("操作失败或者记录未更改");
            }
            // 7. 关闭连接,由于try..with..resource会自动关闭,所以无须担心
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

删除举例:

 public static void main(String[] args) {
        /**
         * 由于Connection和Statement实现了AutoCloseable接口,所以可以使用自动关闭
         */
        try(
                // 2.获取Connection对象
                Connection conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/db3?useSSL=true",
                        "root",
                        "nrblwbb7") ;
                // 3. 获取Statement对象
                Statement stmt = conn.createStatement()
                ){
            // 1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 4. 创建sql
            String sql = "DELETE FROM ACCOUNT WHERE NAME = '网址'";
            // 5. 执行sql
            int count = stmt.executeUpdate(sql);
            // 6. 处理结果
            System.out.println(count);
            if(count > 0){
                System.out.println("删除成功");
            }else{
                System.out.println("删除失败");
            }
            // 7. 关闭连接,由于try..with..resource会自动关闭,所以无须担心
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
外关键字约束(Foreign Key Constraint)

定义了表之间的关系,用来维护两个表之间的一致性的关系。
在创建表时创建Foreign Key Constraint

CREATE TABLE table2(
    s_id VARCHAR(20),
    s_name VARCHAR(12),
    s_tellphone VARCHAR(11),
    s_address VARCHAR(20),
    CONSTRAINT PK_s_id PRIMARY KEY(s_id),
);--首先新建table2,设置s_id为主键

CREATE TABLE table1(
    t_id VARCHAR(12),
    s_id VARCHAR(20),
    score FLOAT,
    CONSTRAINT pk_ts_id PRIMARY KEY(t_id,s_id),--新建table1,对t_id和s_id设置联合主键,键名pk_ts_id
    CONSTRAINT fk_s_id FOREIGN KEY(s_id)--对s_id设置外键fk_s_id
    REFERENCES table2(s_id)--外键fk_s_id外键关联table2的列s_id
    ON DELETE CASCADE--设置在table1的s_id删除时table2的s_id同时删除
    ON UPDATE CASCADE--设置在table1的s_id更新时table2的s_id同时更新
);

注:对table1设置外键关联table2,在插入数据时需要先插入table2的数据,才能成功插入table1的数据。更改table2.s_id数据,table1.s_id数据也会自动改变。但是更改table1.s_id数据,执行时报外键冲突。总之对table1设置外键关联table2后,table1的数据跟着table2走,不能反着来。

添加和删除外键约束同主键。

ResultSet:结果及对象,封装查询结果

功能:

  1. 封装查询结果:
    • boolean next(): 游标向下移动一行,判断当前行是否是最后一行末尾(是否有数据),如果是,则返回false,如果不是则返回true
    • getXxx(参数):获取数据
      • Xxx:代表数据类型 如: int getInt() , String getString()
      • 参数:
        1. int:代表列的编号,从1开始 如: getString(1)
        2. String:代表列名称。 如: getDouble("balance")
    • 注意:
      • 使用步骤:
        1. 游标向下移动一行
        2. 判断是否有数据
        3. 获取数据

举个简单例子:

 @Test
    public void selectDemo(){
        try {
            // 1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 2.创建sql语句
            String sql = "select * from account";
            try(
                    // 3.获取连接对象
                    Connection conn = DriverManager.getConnection(
                            "jdbc:mysql://localhost:3306/db3?useSSL=true",
                            "root",
                            "root");
                    // 4.获取执行sql语句对象
                    Statement stmt = conn.createStatement()
            ){
                // 5.执行sql
                ResultSet rs = stmt.executeQuery(sql);
                // 6.处理结果集
                while(rs.next()){
                    // 获取id
                    int id = rs.getInt("id");
                    // 获取姓名
                    String name = rs.getString("name");
                    // 获取账户余额
                    double balance = rs.getDouble("balance");
                    // 正确的处理应该是封装为对象,进行处理,这就简单展示下
                    System.out.println("id = "   id   ", name = "   name   ", balance = "   balance);
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }
UNIQUE约束

除主键外另一种可以定义唯一约束的类型,允许空值。添加UNIQUE的方法同上,这里只简单举例。

USE test
GO
ALTER TABLE table2
ADD CONSTRAINT uk_s_tellphone
UNIQUE(s_tellphone);
PreparedStatement:执行动态sql的对象

功能:

  1. 执行动态的sql
    1. SQL注入问题:在拼接sql时,有一些sql的特殊关键字参与字符串的拼接。会造成安全性问题
      1. 输入用户随便,输入密码:a' or 'a' = 'a
      2. sql:select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a'
    2. 解决sql注入问题:使用PreparedStatement对象来解决
    3. 预编译的SQL:参数使用?作为占位符
    4. 步骤:
      1. 导入驱动jar包 mysql-connector-java-5.1.45-bin.jar
      2. 注册驱动
      3. 获取数据库连接对象 Connection
      4. 定义sql
        • 注意:sql的参数使用?作为占位符。 如:select * from user where username = ? and password = ?;
      5. 获取执行sql语句的对象 PreparedStatement Connection.prepareStatement(String sql)
      6. 给?赋值:
        • 方法: setXxx(参数1,参数2)
          • 参数1:?的位置编号 从1 开始
          • 参数2:?的值
      7. 执行sql,接受返回结果,不需要传递sql语句
      8. 处理结果
      9. 释放资源
    5. 注意:后期都会使用PreparedStatement来完成增删改查的所有操作
      1. 可以防止SQL注入
      2. 效率更高
CHECK约束

分为表约束和列约束,用于限制字段值在某个范围。

封装工具类

src下db.properties的信息:

# 驱动
driver=com.mysql.jdbc.Driver
# url
url=jdbc:mysql://127.0.0.1:3306/db3?useSSL=true
# 用户
username=root
# 密码
password=root

封装的工具类如下:

public class JDBCUtils {

    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    static{
        try {
            // 读取配置文件
            InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties pro = new Properties();
            pro.load(inputStream);
            // 静态变量赋值
            driver = pro.getProperty("driver");
            url = pro.getProperty("url");
            username = pro.getProperty("username");
            password = pro.getProperty("password");
            // 注册驱动
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接对象
     * @return 返回连接对象
     */
    public static Connection getConn() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    /**
     * 关闭资源
     * @param stmt
     * @param conn
     */
    public static void close(Statement stmt,Connection conn){
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭资源
     * @param rs
     * @param stmt
     * @param conn
     */
    public static void close(ResultSet rs, Statement stmt, Connection conn){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

本文由澳门十大赌场最新排名发布于澳门十大赌场,转载请注明出处:2008从入门到精通,使用脚本输出excel文件的方法

关键词: BBIN视讯直播

上一篇:对变长字段长度选择的一点思考,ROUND函数的使用

下一篇:没有了