Java™ database connectivity (JDBC) is the JavaSoft specification of a standard application programming interface (API) that allows Java programs to access database management systems. The JDBC API consists of a set of interfaces and classes written in the Java programming language.

Using these standard interfaces and classes, programmers can write applications that connect to databases, send queries written in structured query language (SQL), and process the results.

Connecting to a Database

There are two main ways to get a Connection: DriverManager or DataSource.

In real applications, you should use a DataSource rather than DriverManager to get a Connection. DataSource maintains a connection pool so that you can keep reusing the same connection rather than needing to get a new one each time.

						Connection	conn	=	DriverManager.getConnection(
									"jdbc:postgresql://localhost:5432/ocp-book",
									"username",
									"Password20182");
						System.out.println(conn);
			}

Working with a PreparedStatement

It is more convenient to use a PreparedStatement object for sending SQL statements to the database. This special type of statement is derived from the more general class, Statement.

The main feature of a PreparedStatement object is that, unlike a Statement object, it is given a SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled. As a result, the PreparedStatement object contains not just a SQL statement, but a SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.

try	(PreparedStatement	ps	=	conn.prepareStatement(
			"SELECT	*	FROM	exhibits"))	{
			//	work	with	ps
}

Executing a PreparedStatement

• Modifying Data with executeUpdate(): You can use executeUpdate() with any SQL command that does not return a ResultSet. returns int f rows affaected
• Reading Data with executeQuery(): it returns a ResultSet
• Processing Data with execute(): Can run either a query or an update. It returns a boolean so that we know whether there is a ResultSet

Getting Data from a ResultSet

23:	try	(var	ps	=	conn.prepareStatement(sql);
24:				ResultSet	rs	=	ps.executeQuery())	{
25:
26:				while	(rs.next())	{
27:							int	id	=	rs.getInt("id");
28:							String	name	=	rs.getString("name");
29:							idToNameMap.put(id,	name);
30:				}

Calling a CallableStatement

Sometimes you want your SQL to be directly in the database instead of packaged with your Java code. This is particularly useful when you have many queries and they are complex. A stored procedure is code that is compiled in advance and stored in the database.

• Passing an IN Parameter: used for input – var sql = “{call read_names_by_letter(?)}”;
• Returning an OUT Parameter: Used for output – var sql = “{?= call magic_number(?) }”;
• Working with an INOUT Parameter:

50:	var	sql	=	"{call	double_number(?)}";
51:	try	(var	cs	=	conn.prepareCall(sql))	{
52:				cs.setInt(1,	8);
53:				cs.registerOutParameter(1,	Types.INTEGER);
54:				cs.execute();
55:				System.out.println(cs.getInt("num"));
56:	}

Closing Database Resources

it is important to close resources when you are finished with them. This is true for JDBC as well. JDBC resources, such as a Connection, are expensive to create. Not closing them creates a resource leak that will eventually slow down your program.

• Closing a Connection also closes PreparedStatement (or CallableStatement ) and ResultSet.
• Closing a PreparedStatement (or CallableStatement ) also closes the ResultSet .

Putting it all together

import java.sql.*;

/**
 * @author - LeeN
 * PROJECT NAME: JDBCTESTS
 * CREATED ON: Friday 28 January 2022 - 6:05 AM
 */
public class BetterSQLCode {

    private static final String db_url = "jdbc:postgresql://localhost:5432/postgres";
    private static final String db_user = "postgres";
    private static final String db_password = "73fdjjfgjdhjfx";




    public static void main(String[] args) {

        User user = new User(23, "Lee", "Ndegwa", 29683413, "778", "kdjkf");
        BetterSQLCode code = new BetterSQLCode();

        code.create_users_schema();
        code.register_users(user);
        code.get_users();

    }


    public void create_users_schema() {

        String create_users_schema = "CREATE TABLE IF NOT EXISTS users_fix" +
                "(id INTEGER not null," +
                "first_name VARCHAR(255) not null," +
                "last_name VARCHAR(255) not null," +
                "national_id INTEGER not null," +
                "phone_number VARCHAR(255)," +
                "email VARCHAR(255) NOT NULL)";

        try (Connection conn = DriverManager.getConnection(db_url, db_user, db_password)) {
            conn.createStatement().executeUpdate(create_users_schema);
        } catch (SQLException e) {
            System.out.println(e.getNextException());
        }
    }


    public void register_users(User user) {

        String sql_users_registry = "INSERT INTO users_fix VALUES(?,?,?,?,?,?)";

        try (Connection conn = DriverManager.getConnection(db_url, db_user, db_password)) {

            PreparedStatement ps = conn.prepareStatement(sql_users_registry);

            ps.setInt(1, user.getId());
            ps.setString(2, user.getFirst_name());
            ps.setString(3, user.getLast_name());
            ps.setInt(4, user.getNational_id());
            ps.setString(5, user.getPhone_number());
            ps.setString(6, user.getEmail_address());
            ps.executeUpdate();


        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public void get_users(){
        String sql_get_users = "SELECT * FROM users_fix";

        try(Connection conn = DriverManager.getConnection(db_url, db_user, db_password)){
            PreparedStatement ps = conn.prepareStatement(sql_get_users);
            ResultSet rs = ps.executeQuery();

            while (rs.next()){
                int id = rs.getInt(1);
                String name = rs.getString(2).concat(" ").concat(rs.getString(3));

                System.out.println(id + " " + name);
            }


        }catch (SQLException e){
            e.printStackTrace();
        }
    }


}