设置连接mysql和jdbc的最佳方法/模板是什么？

What is the best way to set up connection with mysql's jdbc? And execute simple statement. How to do that? Thank you.

与mysql的jdbc建立连接的最佳方法是什么?并执行简单的声明。怎么做?谢谢。

5 个解决方案

#1

The basic boilerplate for MySQL/JDBC goes something like this:

MySQL / JDBC的基本样板如下所示:

Get the connection:

获得连接:

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://databaseName");

Execute the statement:

执行声明:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * from tableName");
while (rs.next()) {
    System.out.println(rs.getString(1));
}

Close the statement and connection:

关闭语句和连接:

rs.close();
stmt.close();
conn.close();

You just need to make sure you have the driver installed and/or in your CLASSPATH.

您只需确保安装了驱动程序和/或CLASSPATH。

#2

This is the twenty first century - use a JPA (ORM) implementation. But if you insist on going back to the metal (at the risk of down votes) -

这是二十一世纪 - 使用JPA(ORM)实现。但如果你坚持要回到金属(冒着投票的风险) -

There are many ways of getting a JDBC connection from some driver. Using reflection with a hardwired class name is the commonest and perhaps most brain damaged. If you're going to hardwire a class name, you might as well as get the benefits of normal code (compiler catches typos, no extraneous exceptions to deal with, easier to read, explicit dependencies, better tool support, etc).

有很多方法可以从某个驱动程序获取JDBC连接。使用具有硬连线类名的反射是最常见的,也许是大多数脑损伤。如果你要硬连接一个类名,你可以获得正常代码的好处(编译器捕获错别字,没有无关的异常处理,更容易阅读,显式依赖,更好的工具支持等)。

Also get in to the habit of clearing up resources safely.

还要养成安全清理资源的习惯。

So:

public static void main(String[] args) throws SQLException {
     Driver driver = new com.mysql.jdbc.Driver();
     Connection connection = driver.connect(
         "jdbc:mysql://mydatabase", 
         new java.util.Properties() {{
              put("user", "fred");
         }}
     );
     try {
         PreparedStatement statement = connection.prepareStatement(
             "SELECT insideLeg FROM user WHERE name=?"
         );
         try {
             statement.setString(1, "jim");
             ResultSet results = statement.executeQuery();
             try {
                 if (results.next() {
                     System.out.println("= "+results.getLong(1));
                 } else {
                     System.out.println("Missing.");
                 } 
             } finally {
                 results.close();
             }
         } finally {
             statement.close();
         }
     } finally {
         connection.close();
     }
}

What a mess! And it doesn't even use transactions yet. Yes, use an ORM. They're very respectable these days.

真是一团糟!它甚至还没有使用交易。是的,使用ORM。这些天他们非常受人尊敬。

You wont need to do all that for every single statement. You don't want to go around creating instantiating drivers every time. In particular the execute around idiom is useful.

你不需要为每一个声明做所有这些。您不希望每次都创建实例化驱动程序。特别是围绕习语的执行是有用的。

#3

It depends on your case.

这取决于你的情况。

If you simply need to execute some queries from standalone application then you should use single connection like:

如果您只需要从独立应用程序执行某些查询,那么您应该使用单个连接,如:

Class.forName ("yourDriverName");

Connection cn = DriverManager.getConnection ("db url");
Statement st = cn.createStatement ();
ResultSet rs = st.executeQuery ("select * from foo");
while (rs.next()) {
  doSmth ();
} 
rs.close ();
st.close ();
cn.close ();

But if you are developing real application (specially web-application) then use DataSource's. Read manual of your DB and Web-server how to configure datasource. DataSource allows you to use connection-pooling - it'll nessecary to increase performance.

但是,如果您正在开发真正的应用程序(特别是Web应用程序),那么请使用DataSource。阅读数据库和Web服务器的手册如何配置数据源。 DataSource允许您使用连接池 - 它可以提高性能。

Configuring DataSource isn't difficult process.

配置DataSource并不困难。

#4

Here's the sun documentation for creating a JDBC connection. From there it's easy to get access to a Statement object and run some simple SQL.

这是用于创建JDBC连接的sun文档。从那里可以轻松访问Statement对象并运行一些简单的SQL。

For production level systems you'll probably also want to create a connection pool.

对于生产级系统,您可能还需要创建连接池。

#5

Use Spring Framework's JDBC abstraction framework - all you need to do is create a context XML file, and use the JDBC template class. Just a few lines of XML + Java code will get you going. The advantage is keeping your connection details out of compiled Java. See: http://www.springbyexample.org/examples/simple-spring-jdbc-template.html

使用Spring Framework的JDBC抽象框架 - 您需要做的就是创建一个上下文XML文件,并使用JDBC模板类。只需几行XML + Java代码即可实现。优点是保持连接细节不受编译Java的影响。请参阅:http://www.springbyexample.org/examples/simple-spring-jdbc-template.html

#1