精华内容
下载资源
问答
  • java执行sql文件

    2014-11-06 16:56:56
    java执行sql文件
  • java执行sql文件这个问题麻烦的地方在于解析sql脚本文件。所幸的是ant与 ibatis提供了这种实现。 package com.unmi; import java.io.*; import org.apache.tools.ant.*; import org.apache.tools.ant....
    java执行sql文件这个问题麻烦的地方在于解析sql脚本文件。所幸的是ant与 ibatis提供了这种实现。
    

    package com.unmi;
    import java.io.*;
    import org.apache.tools.ant.*;
    import org.apache.tools.ant.taskdefs.*;
    import org.apache.tools.ant.types.*;
    /**
    * 调用 ant.jar 的 SQLExec 执行 SQL 脚本文件
    * @author Unmi
    */
    public class AntExecSql {
    /**
    * @param args
    */
    public static void main(String[] args) {
    SQLExec sqlExec = new SQLExec();
    //设置数据库参数
    sqlExec.setDriver("oracle.jdbc.driver.OracleDriver");
    sqlExec.setUrl("jdbc:oracle:thin:@10.128.x.x:1521:xxsid");
    sqlExec.setUserid("xxuser");
    sqlExec.setPassword("xxpass");
    //要执行的脚本
    sqlExec.setSrc(new File("src/data.sql"));
    //有出错的语句该如何处理
    sqlExec.setOnerror((SQLExec.OnError)(EnumeratedAttribute.getInstance(
    SQLExec.OnError.class, "abort")));
    sqlExec.setPrint(true); //设置是否输出
    //输出到文件 sql.out 中;不设置该属性,默认输出到控制台
    sqlExec.setOutput(new File("src/sql.out"));
    sqlExec.setProject(new Project()); // 要指定这个属性,不然会出错
    sqlExec.execute();
    }
    }
    ibatis:
    package com.ibatis.jpetstore.test;
    import java.sql.DriverManager;
    import java.util.Properties;
    import com.ibatis.common.jdbc.ScriptRunner;
    import com.ibatis.common.resources.Resources;
    import com.mysql.jdbc.Connection;
    public class DBTestSQL {

    public static void main(String[] args) {

    try {
    Properties props = Resources.getResourceAsProperties("properties/database.properties");
    String url = props.getProperty("url");
    String driver = props.getProperty("driver");
    String username = props.getProperty("username");
    String password = props.getProperty("password");
    System.out.println(url);
    if(url.equals("jdbc:mysql://localhost:3306/jpetstore1")) {
    Class.forName(driver).newInstance();
    Connection conn = (Connection) DriverManager.getConnection(url, username, password);
    ScriptRunner runner = new ScriptRunner(conn, false, false);
    runner.setErrorLogWriter(null);
    runner.setLogWriter(null);
    runner.runScript(Resources.getResourceAsReader("ddl/mysql/jpetstore-mysql-schema.sql"));
    runner.runScript(Resources.getResourceAsReader("ddl/mysql/jpetstore-mysql-dataload.sql"));

    }

    } catch (Exception e) {
    e.printStackTrace();
    }

    }
    }


    ScriptRunner(com.ibatis.common.jdbc.*)用法
    ScriptRunner类用于执行SQL语句,例如创建数据库schema,或传入缺省或测试数据库等等。 从下面的例子可以认识到它的易用性:
    例子1:使用现成的数据库连接
    Connection conn=getConnection();//some method to get a Connection
    ScriptRunner runner=new ScriptRunner();
    runner.runScript(conn,Resources.getResourceAsReader("com/some/resource/path/initialize.sql"));
    conn.close();
    例子2:使用新的数据库连接
    ScriptRunner runner=new ScriptRunner("com.some.Driver","jdbc:url://db","login","password");
    runner.runScript(conn,new FileReader("/user/local/db/scripts/initialize-db.sql"));
    例子3:使用新创建的数据连接
    Properties props= getProperties();//some properties form somewhere
    ScriptRunner runner =new ScriptRunner(props);
    runner.runScript(conn,new FileReader("/user/local/db/scripts/initialize-db.sql"));
    展开全文
  • .sql 文件里面存的数据格式如下: ...从别的地方获取到sql文件,将数据插入到你想要的数据库里面, 方法一: 直接在数据库下运行.sql文件: mysql> source /User/ziqiii/myInsert.sql ...

    .sql 文件里面存的数据格式如下:

    INSERT INTO tbl_name (col1, col2, …) VALUES (val1, val2,….), (val3, val4,….)

    从别的地方获取到sql文件,将数据插入到你想要的数据库里面,

    方法一:

    直接在数据库下运行.sql文件:

    mysql> source /User/ziqiii/myInsert.sql

     

    方法二:

    但一般在公司,获取到的.sql文件后,没法直接连数据库插入,需要用代码解决:

     

    首先连接MySQL数据库:

    (里面有个查询示例,main函数可以改成放在其他函数)

    public class Test {
        // MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
    //    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
        static final String DB_URL = "jdbc:mysql://localhost:3306/myDatabase?useUnicode=true&characterEncoding=utf-8";
    
        // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
        static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    
    
        // 数据库的用户名与密码,需要根据自己的设置
        static final String USER = "root";
        static final String PASS = "";
    
        public static void main(String[] args) {
    
            Connection conn = null;
            Statement stmt = null;
            try{
                // 注册 JDBC 驱动
                Class.forName(JDBC_DRIVER);
    
                // 打开链接
                System.out.println("连接数据库...");
                conn = DriverManager.getConnection(DB_URL,USER,PASS);
    
                // 执行查询
                System.out.println(" 实例化Statement对象...");
                stmt = conn.createStatement();
                String sql;
                sql = "SELECT id, a, b FROM table where id < 10";
                ResultSet rs = stmt.executeQuery(sql);
    
                // 展开结果集数据库
                while(rs.next()){
                    // 通过字段检索
                    int id  = rs.getInt("id");
                    String a = rs.getString("a");
                    String b = rs.getString("b");
                    System.out.println("ResultSet: " + rs);
                    // 输出数据
                    System.out.print("ID: " + id);
                    System.out.print(", a: " + a);
                    System.out.print(", b: " + b);
                    System.out.print("\n");
                }
                // 完成后关闭
                rs.close();
                stmt.close();
                conn.close();
            }catch(SQLException se){
                // 处理 JDBC 错误
                se.printStackTrace();
            }catch(Exception e){
                // 处理 Class.forName 错误
                e.printStackTrace();
            }finally{
                // 确认关闭资源
                try{
                    if(stmt!=null && !stmt.isClosed()) stmt.close();
          
                    if(conn!=null && !conn.isClosed()) conn.close();
                    
                    System.out.println("Goodbye!");
                }catch(SQLException se){
                    se.printStackTrace();
                }
            }
            
        }
    

     

    下面用Spring-web的模块,使用@RestController,用get请求触发函数

    (anyway,写在main函数里面也行,随你。看具体实现就行)

      @GetMapping("readSQL")
        public void readSQL() {
            try {
                //原生JDBC开发,获取连接
                Connection conn = getConnection();
    
                String path = "/Users/ziqiiii/Desktop/testFile/00001.sql";
    
                BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(path), "UTF-8"));
                String line = reader.readLine();
           
                Statement stm = conn.createStatement();
                String lineValue = null;
                String sql = null;
            
                while ((lineValue = reader.readLine()) != null) {
                    sql = line+lineValue;
                    sql = sql.substring(0,sql.length()-1);
    
                    System.out.println("-----------------------");
                    System.out.println(sql);
                    System.out.println("-----------------------");
    
                }
                reader.close();
    
            } catch (Exception e) {
                e.printStackTrace();
            }
    
        }
    
    

     

    这里采用处理格式是因为,我的sql文件在存放的具体格式是

    INSERT INTO tbl_name
    VALUES (val1, val2,….),
    (val3, val4,….),
    (val5, val6,….),
    ...
    ...
    (val7, val8,….);

    其他的可以按照具体sql文件格式处理。

     

    参考:

    Java MySQL 连接

    展开全文
  • java读取数据库表中内容转存sql文件,然后解析执行此文件
  • 读取SQL文件并执行

    2020-04-23 09:53:20
    读取固定目录下的所有脚本文件然后执行到指定的数据库 在Spring中做测试有这样一个注解(Sql)...这个注解就是用于读取Sql脚本并执行的,那么参照这个就可以了。 官方注释说明: @Sql is used to annotate a test cl...

    业务需求:读取固定目录下的所有SQL脚本文件然后执行到指定的数据库
    在Spring中做测试有这样一个注解(Sql),如下所示:

    @Sql("classpath:ms_class.sql")
    @Test
    public void contextLoads() {
    
    }
    

    这个注解就是用于读取Sql脚本并执行的,那么参照这个就可以了。
    官方注释说明:

    @Sql is used to annotate a test class or test method to configure SQL scripts() and statements() to be executed against a given database during integration tests.
    Method-level declarations override class-level declarations.
    Script execution is performed by the SqlScriptsTestExecutionListener, which is enabled by default.
    The configuration options provided by this annotation and @SqlConfig are equivalent to those supported by ScriptUtils and ResourceDatabasePopulator but are a superset of those provided by the <jdbc:initialize-database/> XML namespace element. Consult the javadocs of individual attributes in this annotation and @SqlConfig for details.
    Beginning with Java 8, @Sql can be used as a repeatable  annotation. Otherwise, @SqlGroup can be used as an explicit container for declaring multiple instances of @Sql.
    

    大意如下:

    用于注解一个测试类或者测试方法用于在集成测试时配置在一个指定数据库上执行的SQL脚本或者执行语句。方法级别的注解会覆盖类级别的注解。
    这个功能是通过SqlScriptsTestExecutionListener类来实现的,默认是开启的
    可以通过注解@SqlConfig配置脚本解析的各种属性。

    查看SqlScriptsTestExecutionListener类下的方法如下:

    // org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener
    private void executeSqlScripts(Sql sql, ExecutionPhase executionPhase, TestContext testContext, boolean classLevel)
    		throws Exception {
    
    	if (executionPhase != sql.executionPhase()) {
    		return;
    	}
    	// 读取配置信息
    	MergedSqlConfig mergedSqlConfig = new MergedSqlConfig(sql.config(), testContext.getTestClass());
    	if (logger.isDebugEnabled()) {
    		logger.debug(String.format("Processing %s for execution phase [%s] and test context %s.",
    				mergedSqlConfig, executionPhase, testContext));
    	}
    	// 包装各种配置并真实执行数据库操作的类
    	final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
    	populator.setSqlScriptEncoding(mergedSqlConfig.getEncoding());
    	populator.setSeparator(mergedSqlConfig.getSeparator());
    	populator.setCommentPrefix(mergedSqlConfig.getCommentPrefix());
    	populator.setBlockCommentStartDelimiter(mergedSqlConfig.getBlockCommentStartDelimiter());
    	populator.setBlockCommentEndDelimiter(mergedSqlConfig.getBlockCommentEndDelimiter());
    	populator.setContinueOnError(mergedSqlConfig.getErrorMode() == ErrorMode.CONTINUE_ON_ERROR);
    	populator.setIgnoreFailedDrops(mergedSqlConfig.getErrorMode() == ErrorMode.IGNORE_FAILED_DROPS);
    	// 读取环境中的资源配置
    	String[] scripts = getScripts(sql, testContext, classLevel);
    	scripts = TestContextResourceUtils.convertToClasspathResourcePaths(testContext.getTestClass(), scripts);
    	List<Resource> scriptResources = TestContextResourceUtils.convertToResourceList(
    			testContext.getApplicationContext(), scripts);
    	for (String stmt : sql.statements()) {
    		if (StringUtils.hasText(stmt)) {
    			stmt = stmt.trim();
    			scriptResources.add(new ByteArrayResource(stmt.getBytes(), "from inlined SQL statement: " + stmt));
    		}
    	}
    	populator.setScripts(scriptResources.toArray(new Resource[scriptResources.size()]));
    	if (logger.isDebugEnabled()) {
    		logger.debug("Executing SQL scripts: " + ObjectUtils.nullSafeToString(scriptResources));
    	}
    	// 获取数据源 事务
    	String dsName = mergedSqlConfig.getDataSource();
    	String tmName = mergedSqlConfig.getTransactionManager();
    	DataSource dataSource = TestContextTransactionUtils.retrieveDataSource(testContext, dsName);
    	PlatformTransactionManager txMgr = TestContextTransactionUtils.retrieveTransactionManager(testContext, tmName);
    	boolean newTxRequired = (mergedSqlConfig.getTransactionMode() == TransactionMode.ISOLATED);
    
    	if (txMgr == null) {
    		if (newTxRequired) {
    			throw new IllegalStateException(String.format("Failed to execute SQL scripts for test context %s: " +
    					"cannot execute SQL scripts using Transaction Mode [%s] without a PlatformTransactionManager.",
    					testContext, TransactionMode.ISOLATED));
    		}
    		if (dataSource == null) {
    			throw new IllegalStateException(String.format("Failed to execute SQL scripts for test context %s: " +
    					"supply at least a DataSource or PlatformTransactionManager.", testContext));
    		}
    		// Execute scripts directly against the DataSource
    		// 操作数据库
    		populator.execute(dataSource);
    	}
    	else {
    		DataSource dataSourceFromTxMgr = getDataSourceFromTransactionManager(txMgr);
    		// Ensure user configured an appropriate DataSource/TransactionManager pair.
    		if (dataSource != null && dataSourceFromTxMgr != null && !dataSource.equals(dataSourceFromTxMgr)) {
    			throw new IllegalStateException(String.format("Failed to execute SQL scripts for test context %s: " +
    					"the configured DataSource [%s] (named '%s') is not the one associated with " +
    					"transaction manager [%s] (named '%s').", testContext, dataSource.getClass().getName(),
    					dsName, txMgr.getClass().getName(), tmName));
    		}
    		if (dataSource == null) {
    			dataSource = dataSourceFromTxMgr;
    			if (dataSource == null) {
    				throw new IllegalStateException(String.format("Failed to execute SQL scripts for " +
    						"test context %s: could not obtain DataSource from transaction manager [%s] (named '%s').",
    						testContext, txMgr.getClass().getName(), tmName));
    			}
    		}
    		final DataSource finalDataSource = dataSource;
    		int propagation = (newTxRequired ? TransactionDefinition.PROPAGATION_REQUIRES_NEW :
    				TransactionDefinition.PROPAGATION_REQUIRED);
    		TransactionAttribute txAttr = TestContextTransactionUtils.createDelegatingTransactionAttribute(
    				testContext, new DefaultTransactionAttribute(propagation));
    		new TransactionTemplate(txMgr, txAttr).execute(new TransactionCallbackWithoutResult() {
    			@Override
    			public void doInTransactionWithoutResult(TransactionStatus status) {
    				populator.execute(finalDataSource);
    			}
    		});
    	}
    }
    

    上面代码主要做了如下几个事情:

    1. 读取相关配置并设置到ResourceDatabasePopulator 对象中
    2. 读取文件并转换成资源列表(Resource)并设置到ResourceDatabasePopulator 对象中
    3. 获取当前环境中的数据源和事务管理器
    4. 通过ResourceDatabasePopulator和数据源操作数据库

    也就是所有的核心都在以下类里面

    org.springframework.jdbc.datasource.init.ResourceDatabasePopulator 
    

    分析这个类的结构
    在这里插入图片描述
    类的属性中包含了脚本资源、文件编码、分割符(默认;)、SQL脚本的默认单行注释前缀(–)、SQL脚本的默认多行注释开头与结尾,对应源码如下:

    /**
     * Default statement separator within SQL scripts: {@code ";"}.
     */
    public static final String DEFAULT_STATEMENT_SEPARATOR = ";";
    
    /**
     * Fallback statement separator within SQL scripts: {@code "\n"}.
     * <p>Used if neither a custom separator nor the
     * {@link #DEFAULT_STATEMENT_SEPARATOR} is present in a given script.
     */
    public static final String FALLBACK_STATEMENT_SEPARATOR = "\n";
    
    /**
     * End of file (EOF) SQL statement separator: {@code "^^^ END OF SCRIPT ^^^"}.
     * <p>This value may be supplied as the {@code separator} to {@link
     * #executeSqlScript(Connection, EncodedResource, boolean, boolean, String, String, String, String)}
     * to denote that an SQL script contains a single statement (potentially
     * spanning multiple lines) with no explicit statement separator. Note that
     * such a script should not actually contain this value; it is merely a
     * <em>virtual</em> statement separator.
     */
    public static final String EOF_STATEMENT_SEPARATOR = "^^^ END OF SCRIPT ^^^";
    
    /**
     * Default prefix for single-line comments within SQL scripts: {@code "--"}.
     */
    public static final String DEFAULT_COMMENT_PREFIX = "--";
    
    /**
     * Default start delimiter for block comments within SQL scripts: {@code "/*"}.
     */
    public static final String DEFAULT_BLOCK_COMMENT_START_DELIMITER = "/*";
    
    /**
     * Default end delimiter for block comments within SQL scripts: <code>"*&#47;"</code>.
     */
    public static final String DEFAULT_BLOCK_COMMENT_END_DELIMITER = "*/";
    

    也就是说通过这个类,在默认的情况下,支持读取如下的脚本:

    /*
    Navicat MySQL Data Transfer
    
    Source Server         : localhost
    Source Server Version : 50727
    Source Host           : localhost:3306
    Source Database       : ms_class
    
    Target Server Type    : MYSQL
    Target Server Version : 50727
    File Encoding         : 65001
    
    Date: 2020-04-01 14:42:58
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for lesson
    -- ----------------------------
    DROP TABLE IF EXISTS `lesson`;
    CREATE TABLE `lesson` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
      `title` varchar(45) NOT NULL COMMENT '标题',
      `cover` varchar(45) NOT NULL COMMENT '课程封面',
      `price` decimal(10,0) NOT NULL COMMENT '价格',
      `description` varchar(500) NOT NULL,
      `create_time` datetime NOT NULL COMMENT '创建时间',
      `video_url` varchar(255) NOT NULL COMMENT '视频播放地址',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of lesson
    -- ----------------------------
    INSERT INTO `lesson` VALUES ('1', 'SpringCloud视频教程', 'xxx', '5', 'SpringCloud视频教程', '2020-02-15 15:50:35', 'https://ke.qq.com/classroom/index.html');
    
    -- ----------------------------
    -- Table structure for lesson_user
    -- ----------------------------
    DROP TABLE IF EXISTS `lesson_user`;
    CREATE TABLE `lesson_user` (
      `lesson_id` int(11) NOT NULL COMMENT 'leson.id',
      `user_id` int(11) NOT NULL COMMENT 'user.id',
      PRIMARY KEY (`lesson_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of lesson_user
    -- ----------------------------
    INSERT INTO `lesson_user` VALUES ('1', '3');
    
    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Id',
      `username` varchar(45) NOT NULL COMMENT '账号',
      `password` varchar(45) NOT NULL COMMENT '密码',
      `money` decimal(10,0) NOT NULL COMMENT '余额',
      `role` varchar(45) NOT NULL COMMENT '角色',
      `reg_time` datetime DEFAULT NULL COMMENT '注册时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='用户信息表';
    
    -- ----------------------------
    -- Records of user
    -- ----------------------------
    INSERT INTO `user` VALUES ('2', 'jack', '1234', '145', 'user', '2020-02-29 21:27:59');
    INSERT INTO `user` VALUES ('3', 'king', '1234', '145', 'vip', '2020-02-29 21:27:59');
    

    在这个脚本中包含SQL的块注释/**/和单行注释–

    编写一个服务类接口

    public interface SqlExecutorService {
    	/**
    	 * 执行脚本 
    	 * @param resoureLocation 文件路径
    	 */
    	void exec(String resoureLocation);
    
    }
    

    实现这个接口

    import java.io.IOException;
    import java.util.Arrays;
    
    import javax.sql.DataSource;
    
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.core.io.Resource;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.core.io.support.ResourcePatternResolver;
    import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
    import org.springframework.stereotype.Service;
    
    import com.example.durid.demo.service.SqlExecutorService;
    
    @Service
    public class DefaultSqlExecutorService implements SqlExecutorService {
    
    	private static final Logger logger = LoggerFactory.getLogger(SqlExecutorService.class);
    
    	@Autowired
    	private DataSource dataSource;
    
    	private static final ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();
    
    	@Override
    	public void exec(String resoureLocation) {
    		try {
    			Resource[] scripts = resourceResolver.getResources(resoureLocation);
    			ResourceDatabasePopulator populator = new ResourceDatabasePopulator(scripts);
    			populator.setSqlScriptEncoding("utf-8");
    			populator.execute(dataSource);
    			logger.info("执行脚本{}成功", Arrays.toString(scripts));
    		} catch (IOException e) {
    			throw new RuntimeException("执行脚本" + resoureLocation + "异常", e);
    		}
    	}
    }
    

    这个服务类中使用ResourcePatternResolver去读取资源,因为这个类可以读取如下格式的资源,既可以通过匹配符模式,又可以直接指定全路径,非常方便

    classpath*:config/**/*.sql
    classpath*:ms_user.sql
    

    设置使用utf-8编码读取资源,因为默认情况下ResourceDatabasePopulator使用的是当前平台使用的编码,这样会对平台依赖。
    操作注入的数据源。

    如果对执行细节感兴趣的话,可以去跟读源码DatabasePopulatorUtils.execute(this, dataSource),其实也没有多大的学问,主要是字符串处理,怎么把那些注释过滤掉,只保留需要执行的SQL语句。另外分析源码,也可以知道这个工具类读取脚本然后是一句一句执行的,也就是说如果想优化的话,可以考虑批量执行。

    测试一下(当前是一个Spring Boot工程,此处不详细说明):

    @SpringBootApplication
    @MapperScans({ @MapperScan(basePackages = { "com.example.mybatis.demo.mapper" }, annotationClass = Repository.class),
    		@MapperScan(basePackages = { "com.example.durid.demo.mapper" }, annotationClass = Mapper.class) })
    public class DemoApplication implements CommandLineRunner {
    
    	public static void main(String[] args) {
    		SpringApplication.run(DemoApplication.class, args);
    	}
    	
    	@Autowired
    	private SqlExecutorService sqlExecutorService;
    	
    	@Override
    	public void run(String... args) throws Exception {
    		sqlExecutorService.exec("classpath:*.sql");
    	}
    }
    

    然后在application.properties文件中开启对应的日志

    logging.level.org.springframework.jdbc.datasource.init=debug
    

    运行Spring Boot主类,查看控制台日志,有如下信息:

    2020-04-23 09:33:52.150 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from file [D:\20191030\demo\target\classes\ms_class.sql]
    2020-04-23 09:33:52.153 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: SET FOREIGN_KEY_CHECKS=0
    2020-04-23 09:33:52.722 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: DROP TABLE IF EXISTS `lesson`
    2020-04-23 09:33:53.446 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: CREATE TABLE `lesson` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id', `title` varchar(45) NOT NULL COMMENT '标题', `cover` varchar(45) NOT NULL COMMENT '课程封面', `price` decimal(10,0) NOT NULL COMMENT '价格', `description` varchar(500) NOT NULL, `create_time` datetime NOT NULL COMMENT '创建时间', `video_url` varchar(255) NOT NULL COMMENT '视频播放地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
    2020-04-23 09:33:53.557 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO `lesson` VALUES ('1', 'SpringCloud视频教程', 'xxx', '5', 'SpringCloud视频教程', '2020-02-15 15:50:35', 'https://ke.qq.com/classroom/index.html')
    2020-04-23 09:33:53.876 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: DROP TABLE IF EXISTS `lesson_user`
    2020-04-23 09:33:54.274 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: CREATE TABLE `lesson_user` ( `lesson_id` int(11) NOT NULL COMMENT 'leson.id', `user_id` int(11) NOT NULL COMMENT 'user.id', PRIMARY KEY (`lesson_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    2020-04-23 09:33:54.407 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO `lesson_user` VALUES ('1', '3')
    2020-04-23 09:33:54.667 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: DROP TABLE IF EXISTS `user`
    2020-04-23 09:33:55.324 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Id', `username` varchar(45) NOT NULL COMMENT '账号', `password` varchar(45) NOT NULL COMMENT '密码', `money` decimal(10,0) NOT NULL COMMENT '余额', `role` varchar(45) NOT NULL COMMENT '角色', `reg_time` datetime DEFAULT NULL COMMENT '注册时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='用户信息表'
    2020-04-23 09:33:55.395 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO `user` VALUES ('2', 'jack', '1234', '145', 'user', '2020-02-29 21:27:59')
    2020-04-23 09:33:55.480 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO `user` VALUES ('3', 'king', '1234', '145', 'vip', '2020-02-29 21:27:59')
    2020-04-23 09:33:55.481 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from file [D:\20191030\demo\target\classes\ms_class.sql] in 3331 ms.
    2020-04-23 09:33:55.481 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from file [D:\20191030\demo\target\classes\ms_user.sql]
    2020-04-23 09:33:55.483 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: SET FOREIGN_KEY_CHECKS=0
    2020-04-23 09:33:56.187 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: DROP TABLE IF EXISTS `user`
    2020-04-23 09:33:56.703 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Id', `username` varchar(45) NOT NULL COMMENT '账号', `password` varchar(45) NOT NULL COMMENT '密码', `money` decimal(10,0) NOT NULL COMMENT '余额', `role` varchar(45) NOT NULL COMMENT '角色', `reg_time` datetime DEFAULT NULL COMMENT '注册时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='用户信息表'
    2020-04-23 09:33:56.774 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO `user` VALUES ('1', 'itmuch', '1111', '3', 'user', '2020-02-15 14:37:20')
    2020-04-23 09:33:56.919 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO `user` VALUES ('2', 'jack', '1234', '145', 'user', '2020-02-29 21:27:59')
    2020-04-23 09:33:57.094 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO `user` VALUES ('3', 'king', '1234', '125', 'vip', '2020-02-29 21:27:59')
    2020-04-23 09:33:57.413 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: DROP TABLE IF EXISTS `user_account_event_log`
    2020-04-23 09:33:57.953 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: CREATE TABLE `user_account_event_log` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Id', `user_id` int(11) DEFAULT NULL COMMENT 'user.id', `money` decimal(10,0) NOT NULL COMMENT '金额', `event` varchar(20) NOT NULL COMMENT '事件', `create_time` datetime NOT NULL COMMENT '创建时间', `description` varchar(200) NOT NULL COMMENT '描述', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='用户金额流水表'
    2020-04-23 09:33:58.094 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO `user_account_event_log` VALUES ('2', '1', '5', '购买课程', '2020-02-24 09:59:03', '1购买了id为1的课程')
    2020-04-23 09:33:58.144 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO `user_account_event_log` VALUES ('3', '2', '5', '购买课程', '2020-03-01 05:22:33', '2购买了id为1的课程')
    2020-04-23 09:33:58.269 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO `user_account_event_log` VALUES ('4', '2', '5', '购买课程', '2020-03-01 05:35:17', '2购买了id为1的课程')
    2020-04-23 09:33:58.352 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO `user_account_event_log` VALUES ('5', '3', '5', '购买课程', '2020-03-11 12:54:32', '3购买了id为1的课程')
    2020-04-23 09:33:58.442 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO `user_account_event_log` VALUES ('6', '3', '5', '购买课程', '2020-03-11 13:28:22', '3购买了id为1的课程')
    2020-04-23 09:33:58.488 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO `user_account_event_log` VALUES ('7', '3', '5', '购买课程', '2020-03-15 11:05:55', '3购买了id为1的课程')
    2020-04-23 09:33:58.544 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO `user_account_event_log` VALUES ('8', '3', '5', '购买课程', '2020-03-15 12:53:11', '3购买了id为1的课程')
    2020-04-23 09:33:58.546 DEBUG 12744 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from file [D:\20191030\demo\target\classes\ms_user.sql] in 3064 ms.
    2020-04-23 09:33:58.547  INFO 12744 --- [           main] c.e.d.demo.service.SqlExecutorService    : 执行脚本[file [D:\20191030\demo\target\classes\ms_class.sql], file [D:\20191030\demo\target\classes\ms_user.sql]]成功
    

    从以上信息不难看出,执行脚本ms_class.sql, ms_user.sql成功
    在这里插入图片描述
    备注:如果有一个超长的SQL语句,可能跨越多行,需要注意在后面添加分割符(;),否则会抛出异常。

    此处模拟一下:在主目录下添加如下脚本:

    INSERT INTO `user_account_event_log` 
    VALUES ('9', '3', '5', '购买课程', '2020-03-15 12:53:11', '3购买了id为1的课程')
    

    出现如下异常:

    java.lang.IllegalStateException: Failed to execute CommandLineRunner
    	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:787) [spring-boot-2.2.5.RELEASE.jar:2.2.5.RELEASE]
    	at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:768) [spring-boot-2.2.5.RELEASE.jar:2.2.5.RELEASE]
    	at org.springframework.boot.SpringApplication.run(SpringApplication.java:322) [spring-boot-2.2.5.RELEASE.jar:2.2.5.RELEASE]
    	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) [spring-boot-2.2.5.RELEASE.jar:2.2.5.RELEASE]
    	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) [spring-boot-2.2.5.RELEASE.jar:2.2.5.RELEASE]
    	at com.example.durid.demo.DemoApplication.main(DemoApplication.java:20) [classes/:na]
    Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of file [D:\20191030\demo\target\classes\insert.sql]: INSERT INTO `user_account_event_log` ; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
    	at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:622) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    	at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:254) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    	at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:49) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    	at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.execute(ResourceDatabasePopulator.java:269) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    	at com.example.durid.demo.service.impl.DefaultSqlExecutorService.exec(DefaultSqlExecutorService.java:35) ~[classes/:na]
    	at com.example.durid.demo.DemoApplication.run(DemoApplication.java:28) [classes/:na]
    	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:784) [spring-boot-2.2.5.RELEASE.jar:2.2.5.RELEASE]
    	... 5 common frames omitted
    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_121]
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_121]
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_121]
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_121]
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.42.jar:5.1.42]
    	at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.42.jar:5.1.42]
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943) ~[mysql-connector-java-5.1.42.jar:5.1.42]
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[mysql-connector-java-5.1.42.jar:5.1.42]
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[mysql-connector-java-5.1.42.jar:5.1.42]
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) ~[mysql-connector-java-5.1.42.jar:5.1.42]
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[mysql-connector-java-5.1.42.jar:5.1.42]
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486) ~[mysql-connector-java-5.1.42.jar:5.1.42]
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2444) ~[mysql-connector-java-5.1.42.jar:5.1.42]
    	at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845) ~[mysql-connector-java-5.1.42.jar:5.1.42]
    	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745) ~[mysql-connector-java-5.1.42.jar:5.1.42]
    	at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:632) ~[druid-1.1.17.jar:1.1.17]
    	at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:601) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]
    	... 11 common frames omitted
    

    在这里插入图片描述从日志也不难分析出这里将第一行作为了一条语句来执行。
    通过分析源码,主要的问题在ScriptUtils.executeSqlScript这个方法中如下的代码

    if (!EOF_STATEMENT_SEPARATOR.equals(separator) && !containsSqlScriptDelimiters(script, separator)) {
    	separator = FALLBACK_STATEMENT_SEPARATOR;
    }
    

    当脚本中不存在默认的分割符的情况下,分割符会被更换为\n
    有两种方式可以解决上面的问题,
    一种是单行书写脚本
    第二种是添加分割符,修改脚本如下:

    INSERT INTO `user_account_event_log` 
    VALUES ('9', '3', '5', '购买课程', '2020-03-15 12:53:11', '3购买了id为1的课程');
    

    执行程序,出现如下日志

    2020-04-23 09:46:54.279  INFO 276 --- [           main] c.e.d.demo.service.SqlExecutorService    : 执行脚本[file [D:\20191030\demo\target\classes\insert.sql], file [D:\20191030\demo\target\classes\ms_class.sql], file [D:\20191030\demo\target\classes\ms_user.sql]]成功
    

    说明问题解决了。
    在这里插入图片描述

    展开全文
  • Java执行SQL脚本文件到数据库

    万次阅读 2019-02-22 19:57:10
    Java执行SQL脚本文件到数据库 方式一:直接读取SQL脚本文件的内容,然后传递到SQL中。 代码:RunSqlService: @Autowired private RunSqlDao ... * @param sqlPath SQL文件的路径:如:D:/TestProjec...

    Java执行SQL脚本文件到数据库

    方式一:直接读取SQL脚本文件的内容,然后传递到SQL中。

    代码:RunSqlService:

    复制代码

        @Autowired
        private RunSqlDao runSqlDao;
        
        /**
         * 读取文件内容到SQL中执行
         * @param sqlPath SQL文件的路径:如:D:/TestProject/web/sql/脚本.Sql
         */
        public void runSqlByReadFileContent(String sqlPath) throws Exception {
            try {
                 
                String sqlStr = readFileByLines(sqlPath);
                // System.out.println("获得的文本:" + sqlStr);
                if (sqlStr.length() > 0) {
                    runSqlDao.runSqlBySqlStr(sqlStr);
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw e;
            }
        }
        
        /**
         * 以行为单位读取文件,常用于读面向行的格式化文件
         */
        private String readFileByLines(String filePath) throws Exception {
            StringBuffer str = new StringBuffer();
            BufferedReader reader = null;
            try {
                reader = new BufferedReader(new InputStreamReader(
                        new FileInputStream(filePath), "UTF-8"));
                String tempString = null;
                int line = 1;
                // 一次读入一行,直到读入null为文件结束
                while ((tempString = reader.readLine()) != null) {
                    // 显示行号
                    // System.out.println("line " + line + ": " + tempString);
    
                    str = str.append(" " + tempString);
                    line++;
                }
                reader.close();
            } catch (IOException e) {
                e.printStackTrace();
                throw e;
            } finally {
                if (reader != null) {
                    try {
                        reader.close();
                    } catch (IOException e1) {
                    }
                }
            }
    
            return str.toString();
        }

    复制代码

    RunSqlDao :

    复制代码

        /**
         * @param sqlStr
         */
        public void runSqlBySqlStr(String sqlStr) {
            Map<String,Object> map=new HashMap<String,Object>();
            map.put("sqlStr", sqlStr);
            sqlSessionTemplate.selectList("runSql.runSqlBySqlStr", map);
        }

    复制代码

    SQLMap:

    复制代码

    <mapper namespace="runSql">
    
    <select id="runSqlBySqlStr" parameterType="map">
    <![CDATA[ ${sqlStr}]]>
    </select>
    
    </mapper>

    复制代码

    这种写法:只支持数据的变化(新增、修改、删除),且SQL文件内容以begin开始,以end结束。无法更新表字段修改等操作。

    方式二;使用ScriptRunner

    代码:RunSqlService:

    复制代码

        /**
         * 执行sql脚本文件 使用ScriptRunner
         * @param sqlPath SQL文件的路径:如:D:/TestProject/web/sql/脚本.Sql
         */
        public void runSqlByScriptRunner(String sqlPath) throws Exception {
            try {
                SqlSession sqlSession = sqlSessionFactory.openSession();
                Connection conn = sqlSession.getConnection();
                ScriptRunner runner = new ScriptRunner(conn);
                runner.setEscapeProcessing(false);
                runner.setSendFullScript(true);            
                runner.runScript(new InputStreamReader(new FileInputStream(sqlPath), "UTF-8"));
            } catch (Exception e) {
                e.printStackTrace();
                throw e;
            }
        }

    复制代码

    这种写法:只能有一行SQL,即一次执行一个SQL语句,否则就会报错。

    方式三:使用ScriptUtils
    代码:RunSqlService:(以下两种方式:脚本.Sql 和RunSqlService 在同一目录下)

    方法(1)

    复制代码

        /**
         * 执行sql脚本文件 使用Spring工具类
         */
        public void runSqlBySpringUtils() throws Exception {
            try {
                SqlSession sqlSession = sqlSessionFactory.openSession();
                Connection conn = sqlSession.getConnection();
                ClassPathResource rc = new ClassPathResource("脚本.Sql", RunSqlDao.class);
                ScriptUtils.executeSqlScript(conn, rc);
            } catch (Exception e) {
                e.printStackTrace();
                throw e;
            }
        }

    复制代码

    方法(2)

    复制代码

        /**
         * 执行sql脚本文件 使用Spring工具类
         */
        public void runSqlBySpringUtils() throws Exception {
            try {
                SqlSession sqlSession = sqlSessionFactory.openSession();
                Connection conn = sqlSession.getConnection();
                ClassPathResource rc = new ClassPathResource("脚本.Sql", RunSqlDao.class);
                EncodedResource er = new EncodedResource(rc, "utf-8");
                ScriptUtils.executeSqlScript(conn, er);
            } catch (Exception e) {
                e.printStackTrace();
                throw e;
            }
        }
    
    

    复制代码

     加载的SQL文件不在class下

    复制代码

       /**
         * @param sqlPath
         */
        public void runsqlBySpringUtils(String sqlPath) {
            try {
                SqlSession sqlSession = sqlSessionFactory.openSession();
                Connection conn = sqlSession.getConnection();
                
                FileSystemResource rc = new FileSystemResource(sqlPath);
                EncodedResource er = new EncodedResource(rc, "GBK");
                ScriptUtils.executeSqlScript(conn, er);
            } catch (Exception e) {
                e.printStackTrace();
                throw e;
            }
        }

    复制代码

      方法(1),脚本.Sql文件必须是ANSI的,否则执行到数据中汉字是乱码。

     方法(2)解决了方法(1)的问题

    展开全文
  • 主要介绍了Spring执行sql脚本文件的方法,小编觉得挺不错的,现在分享给大家,也给大家做个参考。一起跟随小编过来看看吧
  • java读取sql脚本文件并写入数据库

    万次阅读 2016-06-29 11:11:03
    1.java文件防止中文乱码:保证文件、数据库、表属性字段统一编码;之后创建连接时候指定编码 useUnicode=true&characterEncoding=utf-8 2.BufferReader 逐行读取,批量插入数据库
  • java读取sql文件写入mysql数据库

    千次阅读 2018-01-19 11:21:54
    1、导入c3p0-0.9.1.2.jar 、mysql-connector-java-5.1.28-bin.jar 两个数据库连接jar包(需要的话自己上网找或者留言我给发)2、写c3p0配置文件 jdbc:mysql://localhost:3306/dataanaysismodel ...
  • Java读取sql脚本写入数据库

    千次阅读 2019-07-28 02:02:51
    在做软件部署或者安装的时候,有时候需要手动去导入数据库的sql脚本,涉及到多个库的时候手动太low,这里做了一个简单的建表建库操作。...下面介绍两种方法,一种直接导入,一种直接执行sql,实...
  • java直接调用并执行sql脚本

    千次阅读 2018-12-08 14:33:13
    直接上代码 ... import java.io.File; import java.io.FileReader; import java.io.Reader;...import java.nio.charset.Charset;...import java.sql.Connection; import java.sql.DriverManager; impor...
  • * @param sqlPath SQL文件的路径:如:D:/TestProject/web/sql/脚本.Sql */ public void runSqlByReadFileContent(String sqlPath){ try { ArrayList<String> sqlStr = readFileByLines(sqlPath); if (sqlStr....
  •  * 读取sql文件,获取sql语句  39    * 返回所有sql语句的list集合  40    *  */  41     private  List < String >  loadSql(String sqlFile)  throws  Exception {  42    List ...
  • java执行项目中的sql文件

    千次阅读 2017-08-02 16:26:40
    一、首先引入ibatis-common-2.jar包import com.ibatis.common.jdbc.ScriptRunner; import com.ibatis.common.resources.Resources; 二、jpetstore测试代码如下: ... import java.sql.DriverManager;
  • 前言 这是一个非常小的技巧, 但是应该还是有点用。 怎么在SparkSQL中写SQL? 如果我们直接使用,那么大概是如下这样: ss.sql("select * from ...通过如下方式,我们可以将SQL语言注入SQL的特性 image.png ...
  • java连接SqlServer完整代码,工具类,jdbc JDBC工具类的构建 1.资源释放 见https://mp.csdn.net/postedit/86577443 2.驱动防二次注册 ``` Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //...
  • java读取excel文件并写入.txt文件

    千次阅读 2019-08-22 17:33:46
    需求:最近一直要提交很多sql工单数据给DB管理员执行,一个一个复制很麻烦,于是写下此方法!如有高见欢迎留言! 注意:记得引入poi包 如果你觉着不错,麻烦帮忙点个赞!分享下!谢谢! package ...
  • Java读取解析.sql文件

    千次阅读 2014-05-23 13:54:07
    如果您使用的.sql文件是从数据库中导出的,例如 那么需要进行如下更改 首先,去掉 "USE [***]"; 然后,替换所有的所有的“GO”为“”、去掉注释,以及print语句 最后,在每一条sql语句后面...
  • java的控制台中看到的sql日志通常是如下的样子, Preparing: SELECT in (0,1) ORDER BY i.UPDATE_DATE_TIME DESC 2019-01-18 10:12:52,566 [DEBUG](org.apache.ibatis.logging.log4j.Log4jImpl.debug(Log4jImpl....
  • java程序执行SQL脚本文件

    千次阅读 2016-08-17 14:16:17
    1.纯java代码引用文件流的方式(缺点是分割的考虑比较麻烦) [java] view plain copy   package com.unmi.db;    import java.io.FileInputStream;  import java.io....
  • Android读取sql文件并导入数据库

    千次阅读 2017-11-16 11:44:35
    sql文件一般不应该由Android直接执行的,正常应该是先转成db文件直接放入Android中,Android只需执行一些简单sql命令。...这个还是很简单的,从assets读取sql文件,转成String,获取writeableDatabase,然后执行
  • HSSF - 读写Microsoft Excel XLS格式 07office以前的版本 XSSF - 读写Microsoft Excel OOXML XLSX格式 07office以后的版本 HWPF- 读写Microsoft Word DOC格式 针对的word文档 HSLF - 读写Micorsoft PowerPoint格式 ...
  • 最近遇到需要将SQLServer数据库中的数据迁移到另外一台服务器上的业务场景。经过各方查阅资料,发现大部分导入导出功能都是在第三方数据库连接工具上实现的,不能满足我的需求。于是在下经过各种尝试终于实现了使用...
  • java执行mysqldump步骤: 1.服务器上需要安装mysql客户端: (1)windows系统安装mysql客户端配置环境变量; (2)linux系统安装mysql客户端,不需要配置环境变量;2.java拼接mysqldump命令:/** * * @Title...
  • java创建数据库和执行sql脚本

    千次阅读 2019-02-22 22:58:05
    package sql;...import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import org.apache.tools.ant.Project; import org.apache.tools.ant.taskdefs.SQLE...
  • javasqlserver的image字段存储和读取
  • 数据文件内容data.txt {USER_TYPE=1,CREATE_USER=ZHANG,UPDATE_USER=li,OPER_NUM=D001,SRC=2,UPDATE_TIME=2018-11-11 18:08:08.0,TABLE_NUM=T17,OPTIONS=FIND,PRIMARY_Key=dfewew7e-6hs3-j2j3-de9232jh,PHONE=...
  • 利用MyBatis的ScriptRunner执行sql文件

    千次阅读 2018-04-03 15:37:16
    利用MyBatis的ScriptRunner执行sql文件 利用MyBatis的ScriptRunner执行sql文件 使用原因 为什么选择scriptRunner? 纯java代码引用文件流的方式 ant执行SQL脚本 ibatis方式执行脚本(scriptRunner) 使用...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 166,087
精华内容 66,434
关键字:

java读取sql文件并执行

java 订阅