Java Mysql查询数据库与连接 [英] Java Mysql query database with connection

问题描述

感谢您提供任何帮助。我在Eclipse中使用java创建一个Database - Client应用程序。我使用MySQL 5.6为我的数据库。我有一个方法来创建和返回我将用于查询数据库的连接对象，以及一个方法来返回表中的所有行作为JSON数组。尝试调用连接方法时，问题出在查询方法中。

Thanks in advance for any help. I am creating a Database - Client application using java in Eclipse. I am using MySQL 5.6 for my database. I have a method to create and return a Connection Object that I will use for querying the database, and a method to return all of the rows in the table as a JSON array. The problem comes in the query method when trying to call the connection method.

在：try {
con.getDBConnection（）;
它告诉我有一个错误的getDBConnection（）;并且它给出的建议是添加cast到con。

at: try{ con.getDBConnection(); its telling me there is an error for getDBConnection(); and the suggestions that it gives is to add cast to 'con'.

，我无法从主方法中获取查询方法。

and I can't get the query method to compile from the main method.

package binaparts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.*;
import org.json.JSONArray;

import binaparts.util.ToJSON;

public class returnAllParts extends DBConnect{

    public JSONArray queryReturnAllParts() throws Exception{

        PreparedStatement query = null;
        Connection con = null;

        ToJSON converter = new ToJSON();
        JSONArray json = new JSONArray();

        try{
            con.getDBConnection();
            query = con.prepareStatement("SELECT * " + "from `parts list`" );

            ResultSet rs = query.executeQuery();

            json = converter.toJSONArray(rs);
            query.close();
        }catch(SQLException SQLex){
                SQLex.printStackTrace();
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            if(con != null){
                con.close();
            }
        }
        return json;
    }
}

dao包代码如下：

package binaparts.dao;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.mysql.jdbc.PreparedStatement;

public class DBConnect {

    private Statement st = null;
    private ResultSet rs = null;
    private Connection con = null;  
    private PreparedStatement pst = null;

    private String serverName = "localhost";
    private String portNumber = "3306";
    private String dbms = "mysql";
    private Object userName = "dwilson";
    private Object password = "abc";

    public Connection getDBConnection() throws SQLException {

        Properties connectionProps = new Properties();
        connectionProps.put("user", this.userName);
        connectionProps.put("password", this.password);

            try{
                con = DriverManager.getConnection("jdbc:" + this.dbms + "://" + this.serverName + ":" + this.portNumber + "/", connectionProps);
            }catch(Exception ex){
                ex.printStackTrace();
                con = null;
            }finally{
                if(con != null){
                     System.out.println("Connected to database");
                }
            }
        return con;
    }
    public String getUser(){
        try{
            DatabaseMetaData dmd = con.getMetaData();
            String username = dmd.getUserName();
            //System.out.println("Current User: "+username);
            return username;
        }catch(Exception ex){
            System.out.println(ex);
            ex.printStackTrace();
            return null;
        }
    }
}

p>

main method below:

public class Main{

    public static void main(String[] args){

        DBConnect con = new DBConnect();
        try {
            con.getDBConnection();
            System.out.println(con.getUser());
            System.out.println(con.queryReturnAllParts());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        //Login loginGUI = new Login();
        //MainFrames m = new MainFrames();
        //m.displayGUI();
    }
}

System.out。 println（con.getUser（））; 在工作

推荐答案

> returnAllParts＃queryReturnAllParts 方法，更改

In your returnAllParts#queryReturnAllParts method, change

con.getDBConnection();

由

con = getDBConnection();

问题是 con 从 java.sql.Connection ，它没有 getDBConnection 方法。由于您当前的 returnAllParts 类扩展了 DBConnect 类，它可以访问 public Connection getDBConnection 方法没有问题。

The problem is that con is a variable from java.sql.Connection and it doesn't have a getDBConnection method. Since your current returnAllParts class extends DBConnect class, it can access to the public Connection getDBConnection method with no problems.

System.out.println（con.getUser（））;工作

The System.out.println(con.getUser()); does work

这是因为在您的 Main＃main 已声明 DBConnect con 。不要将此变量与其他方法中声明的 con 变量混淆。

This is because in your Main#main class, you have declared DBConnect con. Do not confuse this variable with the con variable declared in other methods.

与问题无直接关系，但我建议您对目前的程式码/设计进行一些改进：

Not directly related to the problem, but I suggest you some improvements to your current code/design:

• 更改您的名称 returnAllParts 类为将来的读者更有意义的东西（即使你在某些天或几周内将成为你的代码的未来读者）。从读取您的代码，看起来这个类应该重命名为 PartList 。


• 使用数据库连接池而不是手动连接。有类似 BoneCP 的图书馆。


• 可能是您新的到编程，所以最好是以正确的方式开始，并在层中开发您的应用程序（进一步阅读：多层架构）。有了这个基础，我们可以说，DAO（或数据服务，取决于你如何命名）应该只包含访问和检索数据的方式，其他客户端可以使用它，因为他们想要/需要的方法，所以它会是更好地返回 List< PartList> 对象，并且应用程序中的另一个图层（可能最接近演示文稿）将应用从对象到JSON字符串的转换。 >
  
• 从设计的角度来看，如果您的数据库访问对象使用 DBConnect 对象，它将更好从它延伸。这样，对于与所有相关DAO相关联的数据库连接配置，您可以有一个 DBConnect 对象。

• Change the name of your returnAllParts class for something more meaningful for future readers (even you in some days or weeks will become in a future reader of your code). From reading your code, it looks like this class should be renamed to PartList.
• Use a Database connection pool instead of manually get your connections. There are libraries that handle this for your like BoneCP
• Probably you're new to programming, so it would be better that you start in the right way and develop your application in layers (further reading: Multitier architecture). With this basis, we can say that a DAO (or data service, depends how you name it) should only contain the methods to access and retrieve the data in a way other clients could consume it as they want/need, so it would be better returning a List<PartList> object and that another layer in your application (probably the closest to presentation) will apply the transformation from your objects to a JSON String.
• For a design point of view, it would be way better if your database access objects uses a DBConnect object instead of extending from it. In this way, you could have a single DBConnect object per database connection configuration associated to all the related DAOs.

这篇关于Java Mysql查询数据库与连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！