如何在java中运行mysql脚本?

pcww981p  于 2021-06-21  发布在  Mysql
关注(0)|答案(2)|浏览(474)

我有一个mysql脚本,当我连接它的时候,一切正常,但是它给出了一个错误,即“testdb.users”不存在。由此我得出结论,我没有将mysql脚本连接到类。如何正确执行此操作?这是我的课,我在那里得到连接。

private static DBManager instance;

    public static synchronized DBManager getInstance() throws DBException {
        if (instance == null) {
            instance = new DBManager();
        }
        return instance;
    }

    private DBManager() throws DBException {
        try {
            Context initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:/comp/env");
            // ST4DB - the name of data source
            ds = (DataSource) envContext.lookup("jdbc/testDB_MySQL");
            LOG.trace("Data source ==> " + ds);
        } catch (NamingException ex) {
            ex.printStackTrace();
            LOG.error(Messages.ERR_CANNOT_OBTAIN_DATA_SOURCE, ex);
            throw new DBException(Messages.ERR_CANNOT_OBTAIN_DATA_SOURCE, ex);
        }
    }

    private DataSource ds;
public Connection getConnection() throws DBException {
        Connection con = null;
        try {
            con = ds.getConnection();
        } catch (SQLException ex) {
            ex.printStackTrace();
            LOG.error(Messages.ERR_CANNOT_OBTAIN_CONNECTION, ex);
            throw new DBException(Messages.ERR_CANNOT_OBTAIN_CONNECTION, ex);
        }
        return con;
    }

public User findUserByLogin(String login) throws DBException {
        User user = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Connection con = null;
        try {
            con = getConnection();
            pstmt = con.prepareStatement(SQL_FIND_USER_BY_LOGIN);
            pstmt.setString(1, login);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                user = extractUser(rs);
            }
            con.commit();
        } catch (SQLException ex) {
            rollback(con);
            ex.printStackTrace();
            throw new DBException(Messages.ERR_CANNOT_OBTAIN_USER_BY_LOGIN, ex);
        } finally {
            close(con, pstmt, rs);
        }
        return user;
    }

mysql脚本:

CREATE TABLE roles(

        id INTEGER NOT NULL PRIMARY KEY,
        name VARCHAR(10) NOT NULL UNIQUE
    );
    INSERT INTO roles VALUES(0, 'admin');
    INSERT INTO roles VALUES(1, 'client');

    CREATE TABLE users(
        id INTEGER NOT NULL generated always AS identity PRIMARY KEY,
        login VARCHAR(10) NOT NULL UNIQUE,
        password VARCHAR(10) NOT NULL,
        first_name VARCHAR(20) NOT NULL,
        last_name VARCHAR(20) NOT NULL,

            ON DELETE CASCADE
            ON UPDATE RESTRICT
    );

    INSERT INTO users VALUES(DEFAULT, 'admin', 'admin', 'Harry', 'Potter', 0);
    INSERT INTO users VALUES(DEFAULT, 'client', 'client', 'Peter', 'Parker', 1);

context.xml属性文件

我需要使用数据源。如何在我的类中运行mysql脚本?或者还有什么其他的方法?

juzqafwq

juzqafwq1#

您必须解析脚本并自己运行,一次一行。
这可能与您有关:是否有工具可以一次执行一条sql脚本语句?
我已经写了我自己的java代码来做这个。。。我可能应该把它清理干净,然后把它发布到github上它在github上是可用的。

ymdaylpp

ymdaylpp2#

可以使用一个配置文件来实现这一点,就像在derby db中使用的那样?
ant-derby.xml文件

<project name="Derby">
<!--
 Derby home (we use the internal JavaDB of JDK (= Apache Derby) 
-->
    <property name="derby.home" value="C:\Program Files\Java\jdk1.7.0_67\db" />
    <!--  Derby libraries  -->
    <path id="derby.classpath">
        <fileset dir="${derby.home}/lib">
            <include name="*.jar" />
        </fileset>
    </path>
    <!--  To start/stop derby server script (macros)  -->
    <macrodef name="server">
        <attribute name="arg" />
        <sequential>
            <java classname="org.apache.derby.drda.NetworkServerControl">
                <arg value="@{arg}" />
                <classpath refid="derby.classpath" />
            </java>
        </sequential>
    </macrodef>
    <!--  To start the Derby server  -->
    <target name="server-start">
        <server arg="start" />
    </target>
    <!--  To stop the Derby server  -->
    <target name="server-stop">
        <server arg="shutdown" />
    </target>
    <!--  To run script (macros)  -->
    <macrodef name="run">
        <attribute name="script" />
        <sequential>
            <java classname="org.apache.derby.tools.ij" fork="on">
                <arg value="@{script}" />
                <classpath refid="derby.classpath" />
            </java>
        </sequential>
    </macrodef>
    <!--  To run db-create.sql script  -->
    <target name="run-testing">
        <run script="sql/Testing.sql" />
    </target>
    <!--  To select run select.sql script  -->
    <target name="run-select">
        <run script="sql/select.sql" />
    </target>
</project>

这应该可以帮助我控制连接池。还有别的办法吗?

相关问题