Java jdbc cannot Insert to database

Question

I have 2 classes ConnectionUtil, DemoResultSet. The problem is when I run insert 2 times().Then just only 1 record is inserted to database. The second insert create error: "Error:The connection is closed.". So when I uncomment close() statement. It run nice. I don't know what is the trouble. Anyone give me some idea

public class ConnectionUtil {

    private static String url = "jdbc:sqlserver://localhost:1433;databaseName=Northwind";
    private static String username = "user";
    private static String pw = "pass";
    private static Connection conn = null;

    static {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ConnectionUtil.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static Connection getConnection() {
        if (conn == null) {
            try {
                conn = DriverManager.getConnection(url, username, pw);
            } catch (SQLException ex) {
                Logger.getLogger(ConnectionUtil.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return conn;
    }
}

public class DemoResultSet {

    private static ResultSet rs = null;
    private static Connection conn = null;

    public static void initialize() {
        try {
            conn = ConnectionUtil.getConnection();
            Statement statement = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE );
            String sql = "select CategoryID, CategoryName from Categories";
            rs = statement.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(DemoResultSet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void main(String[] args) {
        initialize();
        insert();
        insert();
    }

    public static void insert() {
        try {
            rs.moveToInsertRow();
            rs.updateString("CategoryName", "Test C1008G3");
            rs.insertRow();
            System.out.println("Inserted");
            conn.close(); //uncomment it's okay
        } catch (SQLException ex) {
            System.out.println("Error:" + ex.getMessage());
        }
    }
}

Numeron · Accepted Answer · 2013-10-01 07:44:54Z

2

You are closing your connection inside the insert, so the second insert happens on a closed connection.

The conn.close(), should really be placed like so:

public static void main(String[] args) throws Exception{
    try{
        initialize();
        insert();
        insert();
    }
    finally{
        conn.close();
    }
}

So that you can guarantee the connection is closed properly in the event of any problems.

answered Oct 1, 2013 at 7:44

Numeron

8,8513 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scary Wombat · Accepted Answer · 2013-10-01 07:44:58Z

0

in your insert method you have conn.close(); //uncomment it's okay

But its not!

After you have closed this connection you do not open it again.

answered Oct 1, 2013 at 7:44

Scary Wombat

44.9k7 gold badges38 silver badges67 bronze badges

Comments

bmurauer · Accepted Answer · 2013-10-01 07:45:37Z

0

the jdbc connection is a somewhat heavy object and the idea behind it is not not close it after every operation, but rather reuse the connection for multiple tasks. you should close your connection when your application is done. If you insist on own connections, you have to re-initialize after every close.

answered Oct 1, 2013 at 7:45

bmurauer

1,3491 gold badge13 silver badges28 bronze badges

Comments

Stultuske · Accepted Answer · 2013-10-01 07:45:40Z

0

the problem is you are trying to write to a Connection after closing said connection. you can uncomment that line, but that's just bad practice. my advice: call the initialize(); method not from inside the main method, but as first statement in the insert method.

that way, you are sure you have the connection every time you need it.

answered Oct 1, 2013 at 7:45

Stultuske

9,4691 gold badge28 silver badges38 bronze badges

1 Comment

haind Over a year ago

It show: Oct 01, 2013 4:34:20 PM ResultSetDemo.DemoResultSet initialize Error:The connection is closed. SEVERE: null

Collectives™ on Stack Overflow

Java jdbc cannot Insert to database

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related