java 如何在Java中将ResultSet转换为字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32806432/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert ResultSet to string in Java
提问by ??? Gemini ???
I want to convert resultset to string. After that, I will use the string to write a html file.Course is a table contain courseid(String),name(String),prerequisites(String) connect database is ok. Here is my code and idea. Can you evaluate my idea or give me some better solution?
我想将结果集转换为字符串。之后，我将使用字符串写一个html文件。课程是一个表，包含courseid(String),name(String),prerequisites(String)连接数据库就可以了。这是我的代码和想法。你能评估我的想法或给我一些更好的解决方案吗？
private static void printRecordFromCourse() throws SQLException {
Connection dbConnection = null;
Statement stmt = null;
String printSQL = "SELECT * FROM course";
try {
dbConnection = getDBConnection();
stmt = dbConnection.createStatement();
ResultSet rs=stmt.executeQuery(printSQL);
while(rs.next()){
//Retrieve by column name
String courseid = rs.getString("courseid");
String name = rs.getString("name");
String prerequisites = rs.getString("prerequisites");
String result+ = "<tr><td>"+courseid+"</td><td>"+name+"</td><td>"+prerequisites"</td></tr>";
}
rs.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (stmt != null) {
dbConnection.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
回答by Patrick Wei?
You could use an ArrayList and store the columns in there, such as:
您可以使用 ArrayList 并将列存储在其中，例如：
List allRows = new ArrayList();
while(rs.next()){
String[] currentRow = new String[numberColumns];
for(int i = 1;i<=numberColumns;i++){
row[i-1]=rs.getString(i);
}
rows.add(row);
}
Your ArrayList now contains String arrays, where each array represents one row. Now you can simply transform the string array entries into strings, e.g. using Arrays.toString(allRows.get(i));
您的 ArrayList 现在包含 String 数组，其中每个数组代表一行。现在您可以简单地将字符串数组条目转换为字符串，例如使用Arrays.toString(allRows.get(i));
回答by Basil Bourque
I have not tried your code, but it looks workable at a glance.
我没有试过你的代码，但乍一看似乎可行。
Design-wise, we usually want to separate database access from other logic.
在设计方面，我们通常希望将数据库访问与其他逻辑分开。
And likely you will want to define a class Courseto hold this data and your business logic that operates on that data. For example, you may choose to implement a toHtmlTableRowmethod on Coursethat generates the HTML source. (In a more complex or sophisticated environment, you might also want to move that HTML-generation functionality to another class.) Something like this:
并且您可能希望定义一个类Course来保存这些数据以及您对该数据进行操作的业务逻辑。例如，您可以选择实施toHtmlTableRow方法上Course生成的HTML源代码。（在更复杂或更复杂的环境中，您可能还想将该 HTML 生成功能移至另一个类。）类似于以下内容：
class Course {
String id, name, prereq;
public Course ( String id , String name , String prereq ) {
this.id = id;
this.name = name;
this.prereq = prereq;
}
public CharSequence toHtmlTableRow () {
StringBuilder html = new StringBuilder();
html.append( "<tr>\n" );
html.append( "<td>" + this.id + "</td><td>" + this.name + "</td><td>" + this.prereq + "</td>\n" );
html.append( "</tr>\n" );
return html;
}
// Override `Object`.
@Override
public String toString () {
return "Course{ " +
"id='" + id + '\'' +
" | name='" + name + '\'' +
" | prereq='" + prereq + '\'' +
" }";
}
}
Here is a complete working example app. For the sake of this demo, I crammed it all into a single .javafile. In real work, I would not.
这是一个完整的工作示例应用程序。为了这个演示，我把它全部塞进了一个.java文件中。在实际工作中，我不会。
This example uses the H2 Database Engine. This example makes an in-memory database that never gets written to storage, again because this is a mere example.
此示例使用H2 数据库引擎。这个例子制作了一个永远不会被写入存储的内存数据库，再次因为这只是一个例子。
Note the use of the try-with-resource& AutoCloseablesyntax found in newer versions of Java to simplify working with JDBC.
请注意在较新版本的 Java 中使用try-with-resource&AutoCloseable语法来简化使用 JDBC 的过程。
package com.basilbourque.example;
import java.sql.*;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
public class DbToText {
public static void main ( String[] args ) {
DbToText app = new DbToText();
app.doIt();
}
private void doIt () {
try {
Class.forName( "org.h2.Driver" );
} catch ( ClassNotFoundException e ) {
e.printStackTrace();
}
List< Course > courses = new ArrayList();
try (
Connection conn = DriverManager.getConnection( "jdbc:h2:mem:db_to_text" ) ;
Statement stmt = conn.createStatement() ;
) {
String sql = "CREATE TABLE course_ ( \n" +
" id_ VARCHAR NOT NULL PRIMARY KEY , \n" +
" name_ VARCHAR NOT NULL , \n" +
" prereq_ VARCHAR NOT NULL \n" +
");";
stmt.execute( sql );
// Insert row.
sql = "INSERT INTO course_ ( id_ , name_ , prereq_ ) VALUES ( ? , ? , ? ) ;";
try (
PreparedStatement preparedStatement = conn.prepareStatement( sql ) ;
) {
preparedStatement.setString( 1 , "C01" );
preparedStatement.setString( 2 , "Course 1" );
preparedStatement.setString( 3 , "None" );
preparedStatement.executeUpdate();
preparedStatement.setString( 1 , "C02" );
preparedStatement.setString( 2 , "Course 2" );
preparedStatement.setString( 3 , "C01" );
preparedStatement.executeUpdate();
preparedStatement.setString( 1 , "C03" );
preparedStatement.setString( 2 , "Course 3" );
preparedStatement.setString( 3 , "C02" );
preparedStatement.executeUpdate();
}
// Query all.
sql = "SELECT * FROM course_";
try ( ResultSet rs = stmt.executeQuery( sql ) ; ) {
while ( rs.next() ) {
//Retrieve by column name
String id = rs.getString( "id_" );
String name = rs.getString( "name_" );
String prereq = rs.getString( "prereq_" );
// Instantiate a `Course` object for this data.
Course c = new Course( id , name , prereq );
courses.add( c );
}
}
} catch ( SQLException e ) {
e.printStackTrace();
}
System.out.println( "List of courses: \n" + courses );
System.out.println( "Courses as HTML table rows: " );
for ( Course course : courses ) {
System.out.println( course.toHtmlTableRow() );
}
}
class Course {
String id, name, prereq;
public Course ( String id , String name , String prereq ) {
this.id = id;
this.name = name;
this.prereq = prereq;
}
public CharSequence toHtmlTableRow () {
StringBuilder html = new StringBuilder();
html.append( "<tr>\n" );
html.append( "<td>" + this.id + "</td><td>" + this.name + "</td><td>" + this.prereq + "</td>\n" );
html.append( "</tr>\n" );
return html;
}
// Override `Object`.
@Override
public String toString () {
return "Course{ " +
"id='" + id + '\'' +
" | name='" + name + '\'' +
" | prereq='" + prereq + '\'' +
" }";
}
}
}
When run.
跑的时候。
List of courses:
[Course{ id='C01' | name='Course 1' | prereq='None' }, Course{ id='C02' | name='Course 2' | prereq='C01' }, Course{ id='C03' | name='Course 3' | prereq='C02' }]
Courses as HTML table rows:
<tr>
<td>C01</td><td>Course 1</td><td>None</td>
</tr>
<tr>
<td>C02</td><td>Course 2</td><td>C01</td>
</tr>
<tr>
<td>C03</td><td>Course 3</td><td>C02</td>
</tr>
