java服务器连接mysql数据库

编辑POM

    <build>        <plugins>            <plugin>                <groupId>org.jooq</groupId>                <artifactId>jooq-codegen-maven</artifactId>                <version>3.7.2</version>                <dependencies>                    <dependency>                        <groupId>mysql</groupId>                        <artifactId>mysql-connector-java</artifactId>                        <version>5.1.38</version>                    </dependency>                    <dependency>                        <groupId>org.jooq</groupId>                        <artifactId>jooq-meta</artifactId>                        <version>3.7.2</version>                    </dependency>                    <dependency>                        <groupId>org.jooq</groupId>                        <artifactId>jooq-codegen</artifactId>                        <version>3.7.2</version>                    </dependency>                    <dependency>                        <groupId>org.jooq</groupId>                        <artifactId>jooq</artifactId>                        <version>3.7.2</version>                    </dependency>                </dependencies>                <configuration>                    <!-- JDBC connection parameters -->                    <jdbc>                        <driver>com.mysql.jdbc.Driver</driver>                        <url>jdbc:mysql://数据库连接:端口/?user=myUser</url>                        <user>myUser</user>                        <password>myPassword</password>                    </jdbc>                    <!--jdbc>                        <driver>com.mysql.jdbc.Driver</driver>                        <url>jdbc:mysql://localhost:3306/?user=myUser</url>                        <user>myUser</user>                        <password>myPassword</password>                    </jdbc-->                    <!-- Generator parameters -->                    <generator>                        <name>org.jooq.util.DefaultGenerator</name>                        <database>                            <name>org.jooq.util.mysql.MySQLDatabase</name>                            <includes>.*</includes>                            <inputSchema>mySchema</inputSchema>                        </database>                        <target>                            <packageName>com.test.maverproject.generated.jooq</packageName>                            <directory>src/main/java</directory>                        </target>                    </generator>                </configuration>            </plugin>        </plugins>    </build>    <dependencies>        <!-- HikariCP is our jdbc connection pooler -->        <dependency>            <groupId>com.zaxxer</groupId>            <artifactId>HikariCP</artifactId>            <version>2.4.3</version>            <scope>compile</scope>        </dependency>                <!-- jooq makes db access easy and rewardType-safe -->        <dependency>            <groupId>org.jooq</groupId>            <artifactId>jooq</artifactId>            <version>3.9.1</version>        </dependency>    </dependencies>        <!-- database configuration -->        <db.databaseUrl>jdbc:mysql://数据库连接:端口/schema?user=myUser</db.databaseUrl>        <db.databaseUser>myUser</db.databaseUser>        <db.databasePassword>myPassword</db.databasePassword>        <db.datasource>com.mysql.jdbc.jdbc2.optional.MysqlDataSource</db.datasource>        <db.sql.dialect>MYSQL</db.sql.dialect>    </properties>    <properties>

cmd中cd至pom.XML所在文件夹

mvn jooq-codegen:generate

就可以自动由数据库表生产对应java文件，方便使用。

ps:不更新代码，对数据库修改时：可以添加表。可以在已有表最后加行，但是不能插到中间。

编辑TestConfig.properties文件，添加

#databasedb.datasrc = ${db.datasource}db.url = ${db.databaseUrl}db.user = ${db.databaseUser}db.pass = ${db.databasePassword}db.sql.dialect = ${db.sql.dialect}

新建连接数据库用的DSLContextFactory

import javax.inject.Inject;import javax.sql.DataSource;import org.jooq.Configuration;import org.jooq.DSLContext;import org.jooq.SQLDialect;import org.jooq.conf.RenderNameStyle;import org.jooq.conf.Settings;import org.jooq.impl.DSL;import org.jooq.impl.DefaultConfiguration;public class DSLContextFactory {    private final SQLDialect sqlDialect;    private final Settings settings;    private final DataSource dataSource;    private final Configuration configuration;    @Inject    public DSLContextFactory(RenderNameStyle renderNameStyle, SQLDialect sqlDialect, DataSource dataSource) {        this.settings = new Settings();        this.settings.setRenderSchema(false);        this.settings.setRenderNameStyle(renderNameStyle);        this.sqlDialect = sqlDialect;        this.dataSource = dataSource;        this.configuration = new DefaultConfiguration()                .set(this.dataSource)                .set(this.sqlDialect)                .set(this.settings);    }    public DSLContext create() {        return DSL.using(dataSource, sqlDialect, settings);    }    public DSLContext create(Configuration configuration) {        return DSL.using(configuration);    }    public Configuration getConfiguration() {        return configuration;    }}

以及所需注入的三个facetory：

import org.apache.commons.configuration.Configuration;import org.apache.commons.configuration.ConfigurationException;import org.apache.commons.configuration.PropertiesConfiguration;import org.glassfish.hk2.api.Factory;import org.jooq.SQLDialect;public class SQLDialectFactory implements Factory<SQLDialect> {    private final SQLDialect sqlDialect;        public SQLDialectFactory() throws ConfigurationException {        Configuration config = new PropertiesConfiguration("TestConfig.properties");        sqlDialect = SQLDialect.valueOf(config.getString("db.sql.dialect"));    }            @Override    public SQLDialect provide() {        return sqlDialect;    }    @Override    public void dispose(SQLDialect t) {            }    }

import com.zaxxer.hikari.HikariConfig;import com.zaxxer.hikari.HikariDataSource;import javax.inject.Inject;import javax.sql.DataSource;import org.apache.commons.configuration.Configuration;import org.apache.commons.configuration.ConfigurationException;import org.apache.commons.configuration.PropertiesConfiguration;import org.glassfish.hk2.api.Factory;import org.jooq.SQLDialect;import org.slf4j.LoggerFactory;public class DataSourceFactory  implements Factory<DataSource> {    private final String dataSourceClass;    private final String dbUrl;    private final String dbUser;    private final String dbPass;    private final String dbDialect;    @Inject    public DataSourceFactory() {        Configuration configuration;        try {            configuration = new PropertiesConfiguration("TestConfig.properties");        } catch (ConfigurationException e) {            LoggerFactory.getLogger(getClass()).error("Failed to read properties", e);            throw new RuntimeException("Load gs.properties failed", e);        }        dataSourceClass = configuration.getString("db.datasrc");        dbUrl = configuration.getString("db.url");        dbUser = configuration.getString("db.user");        dbPass = configuration.getString("db.pass");        dbDialect = configuration.getString("db.sql.dialect");    }    @Override    public DataSource provide() {        HikariConfig config = new HikariConfig();        config.setRegisterMbeans(true);        config.setMaxLifetime(25 * 60 * 1000);        config.setIdleTimeout(3 * 60 * 1000);        config.setLeakDetectionThreshold(60 * 1000);        config.setConnectionTimeout(10 * 1000);        config.setMaximumPoolSize(30);        config.setDataSourceClassName(dataSourceClass);        config.addDataSourceProperty("url", dbUrl);        config.addDataSourceProperty("user", dbUser);        config.addDataSourceProperty("password", dbPass);        //mysql optimization        if (SQLDialect.MYSQL == SQLDialect.valueOf(dbDialect)) {            config.addDataSourceProperty("cachePrepStmts", true);            config.addDataSourceProperty("prepStmtCacheSize", 250);            config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);        }        HikariDataSource dataSource = new HikariDataSource(config);        return dataSource;    }    @Override    public void dispose(DataSource t) {    }}

import org.glassfish.hk2.api.Factory;import org.jooq.conf.RenderNameStyle;public class RenderNameStyleFactory  implements Factory<RenderNameStyle> {    @Override    public RenderNameStyle provide() {        return RenderNameStyle.AS_IS;    }    @Override    public void dispose(RenderNameStyle t) {    }    }

修改binder

@Override    protected void configure() {        //数据库        bindFactory(DataSourceFactory.class).to(DataSource.class).in(Singleton.class);        bindFactory(RenderNameStyleFactory.class).to(RenderNameStyle.class).in(Singleton.class);        bindFactory(SQLDialectFactory.class).to(SQLDialect.class).in(Singleton.class);        bindAsContract(DSLContextFactory.class).in(Singleton.class);    }

准备完成，可以使用DSLContextFactory建立连接修改数据库。

import static com.viewkingdom.zombiecatchers2.generated.jooq.Tables.AUTHENTICATION;import com.viewkingdom.zombiecatchers2.generated.jooq.tables.records.AuthenticationRecord;@Servicepublic class DatabaseUserStorage implements UserStorage {         @Inject    public DatabaseUserStorage(            final DSLContextFactory dslContextFactory    ){        this.dslContextFactory = dslContextFactory;    }    //插入    public int insert(){        AuthenticationRecord record = dslContextFactory.create().insertInto(AUTHENTICATION)                .set(AUTHENTICATION.VALID_SESSION_NUMBER, 0)                .returning(AUTHENTICATION.USER_ID)                .execute();    }    //更新    public void update(int userId){        AuthenticationRecord record = dslContextFactory.create()                .selectFrom(AUTHENTICATION)                .where(AUTHENTICATION.USER_ID.eq(userId))                .fetchOne();        record.set(AUTHENTICATION.VALID_SESSION_NUMBER, 0);        record.update();    }}

jooq详细文档 Ver3.9

jOOQ教程 (3.3.3jOOQ as a SQL executor)