\nThe above SQL commands will create a Job table and a Person table in the db-instance database.\n<\/p>\n
Steps to interact with the database through JDBC<\/h3>\n\n- Load the database driver.<\/li>\n
- Provide a username and password pair and name of database instance to get a Connection object.<\/li>\n
- Create a Statement object from the Connection object.<\/li>\n
- Use the Statement object to execute SQL commands to interact with the database.<\/li>\n<\/ul>\n
Sample code to interact with the database through the JDBC api.<\/h3>\n\r\nConnection dbConnection = null;\r\ntry {\r\n \/\/ Load the MySQL driver\r\n Class.forName("com.mysql.jdbc.Driver");\r\n \/\/ Connect to the database instance (db-instance)\r\n \/\/ @ localhost with a user account (identified by user and password).\r\n dbConnection = DriverManager.getConnection("jdbc:mysql:\/\/localhost\/"\r\n + "db-instance", "user", "password");\r\n \/\/ Execute a SQL select statement on the database.\r\n Statement sqlStat = dbConnection.createStatement();\r\n ResultSet sqlResult = sqlStat.executeQuery("SELECT * FROM Person");\r\n \/\/ Traverse sqlResult\r\n while(sqlResult .next()) {\r\n \/\/ Get the value of job-id\r\n int jobId = sqlResult.getInt("job-id");\r\n System.out.println("Job ID: " + jobId);\r\n } \/\/ end while\r\n\r\n} catch (ClassNotFoundException cnfe) {\r\n System.out.println(cnfe.getMessage());\r\n} catch (SQLException sqle) {\r\n System.out.println(sqle);\r\n} finally {\r\n \/\/ Free up resources used\r\n if (dbConnection != null) {\r\n try {\r\n dbConnection.close();\r\n } catch (SQLException sqle) {\r\n \/\/ Swallow any exceptions when closing the connection.\r\n } \/\/ end try-catch block\r\n } \/\/ end if\r\n} \/\/ end try-catch block\r\n<\/pre>\nWhat is wrong with the above code?<\/h3>\n
\nBased on the database schema shown earlier, we can see that the job-id column of the Person table can contain null values. However, in the above coding, we are using the getInt()<\/code> method of the ResultSet<\/code> class to retrieve a the job-id value. The int data type being one of Java's primitive types is not able to store the null. On my machine, the getInt()<\/code> method returns 0 when it hits a null on the job-id.\n<\/p>\nHow to solve the problem?<\/h3>\n
\nThere are two ways to detect whether a null value is read.\n<\/p>\n
\n1) Use the wasNull()<\/code> method provided by the ResultSet class.\n<\/p>\n\r\n\/\/ Traverse sqlResult\r\nwhile(sqlResult.next()) {\r\n \/\/ Get the value of job-id\r\n int jobId = sqlResult.getInt("job-id");\r\n \/\/ if jobId is supposed to be null\r\n if (sqlResult.wasNull()) {\r\n System.out.println("Job ID: null");\r\n } else {\r\n System.out.println("Job ID: " + jobId);\r\n }\r\n} \/\/ end while\r\n<\/pre>\n\n2) Use the getObject()<\/code> method instead of the getInt()<\/code> method to retrieve the value of the job-id column. By using the getObject()<\/code> method, we are able to get null values if there any appears.\n<\/p>\n\r\n\/\/ Traverse sqlResult\r\nwhile(sqlResult.next()) {\r\n Object jobId = sqlResult.getObject("job-id");\r\n if (jobId == null) {\r\n System.out.println("Job ID: null");\r\n } else {\r\n System.out.println("Job ID: " + jobId);\r\n } \/\/ end if (jobId == null)\r\n} \/\/ end while\r\n<\/pre>\n\n \n - \n \n
![\"Facebook](\"\/ph\/img\/3rd-party\/social-icons\/Facebook.png\")
Share\n <\/a>\n <\/li>\n - \n \n
![\"Twitter](\"\/ph\/img\/3rd-party\/social-icons\/Twitter.png\")
Tweet\n <\/a>\n <\/li>\n - \n \n
![\"Linkedin](\"\/ph\/img\/3rd-party\/social-icons\/linkedin.png\")
Share\n <\/a>\n <\/li>\n - \n \n
![\"Pinterest](\"\/ph\/img\/3rd-party\/social-icons\/Pinterest.png\")
Save\n <\/a>\n <\/li>\n <\/ul>\n ","protected":false},"excerpt":{"rendered":"Content is king and large software cannot get away from serving content. In this post, I shall document how to use Java to access relational databases, with the MySQL database as an example. <\/p>\n","protected":false},"author":1,"featured_media":1045,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"footnotes":""},"categories":[375],"tags":[6,10,9],"jetpack_featured_media_url":"https:\/\/www.techcoil.com\/blog\/wp-content\/uploads\/java-logo.jpg","jetpack_shortlink":"https:\/\/wp.me\/p245TQ-B","jetpack-related-posts":[],"jetpack_likes_enabled":true,"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/posts\/37"}],"collection":[{"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/comments?post=37"}],"version-history":[{"count":0,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/posts\/37\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/media\/1045"}],"wp:attachment":[{"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/media?parent=37"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/categories?post=37"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/tags?post=37"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Sample code to interact with the database through the JDBC api.<\/h3>\n\r\nConnection dbConnection = null;\r\ntry {\r\n \/\/ Load the MySQL driver\r\n Class.forName("com.mysql.jdbc.Driver");\r\n \/\/ Connect to the database instance (db-instance)\r\n \/\/ @ localhost with a user account (identified by user and password).\r\n dbConnection = DriverManager.getConnection("jdbc:mysql:\/\/localhost\/"\r\n + "db-instance", "user", "password");\r\n \/\/ Execute a SQL select statement on the database.\r\n Statement sqlStat = dbConnection.createStatement();\r\n ResultSet sqlResult = sqlStat.executeQuery("SELECT * FROM Person");\r\n \/\/ Traverse sqlResult\r\n while(sqlResult .next()) {\r\n \/\/ Get the value of job-id\r\n int jobId = sqlResult.getInt("job-id");\r\n System.out.println("Job ID: " + jobId);\r\n } \/\/ end while\r\n\r\n} catch (ClassNotFoundException cnfe) {\r\n System.out.println(cnfe.getMessage());\r\n} catch (SQLException sqle) {\r\n System.out.println(sqle);\r\n} finally {\r\n \/\/ Free up resources used\r\n if (dbConnection != null) {\r\n try {\r\n dbConnection.close();\r\n } catch (SQLException sqle) {\r\n \/\/ Swallow any exceptions when closing the connection.\r\n } \/\/ end try-catch block\r\n } \/\/ end if\r\n} \/\/ end try-catch block\r\n<\/pre>\nWhat is wrong with the above code?<\/h3>\n
\nBased on the database schema shown earlier, we can see that the job-id column of the Person table can contain null values. However, in the above coding, we are using the getInt()<\/code> method of the ResultSet<\/code> class to retrieve a the job-id value. The int data type being one of Java's primitive types is not able to store the null. On my machine, the getInt()<\/code> method returns 0 when it hits a null on the job-id.\n<\/p>\nHow to solve the problem?<\/h3>\n
\nThere are two ways to detect whether a null value is read.\n<\/p>\n
\n1) Use the wasNull()<\/code> method provided by the ResultSet class.\n<\/p>\n\r\n\/\/ Traverse sqlResult\r\nwhile(sqlResult.next()) {\r\n \/\/ Get the value of job-id\r\n int jobId = sqlResult.getInt("job-id");\r\n \/\/ if jobId is supposed to be null\r\n if (sqlResult.wasNull()) {\r\n System.out.println("Job ID: null");\r\n } else {\r\n System.out.println("Job ID: " + jobId);\r\n }\r\n} \/\/ end while\r\n<\/pre>\n\n2) Use the getObject()<\/code> method instead of the getInt()<\/code> method to retrieve the value of the job-id column. By using the getObject()<\/code> method, we are able to get null values if there any appears.\n<\/p>\n\r\n\/\/ Traverse sqlResult\r\nwhile(sqlResult.next()) {\r\n Object jobId = sqlResult.getObject("job-id");\r\n if (jobId == null) {\r\n System.out.println("Job ID: null");\r\n } else {\r\n System.out.println("Job ID: " + jobId);\r\n } \/\/ end if (jobId == null)\r\n} \/\/ end while\r\n<\/pre>\n\n \n - \n \n Share\n <\/a>\n <\/li>\n
- \n \n Tweet\n <\/a>\n <\/li>\n
- \n \n Share\n <\/a>\n <\/li>\n
- \n \n Save\n <\/a>\n <\/li>\n <\/ul>\n ","protected":false},"excerpt":{"rendered":"
Content is king and large software cannot get away from serving content. In this post, I shall document how to use Java to access relational databases, with the MySQL database as an example. <\/p>\n","protected":false},"author":1,"featured_media":1045,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"footnotes":""},"categories":[375],"tags":[6,10,9],"jetpack_featured_media_url":"https:\/\/www.techcoil.com\/blog\/wp-content\/uploads\/java-logo.jpg","jetpack_shortlink":"https:\/\/wp.me\/p245TQ-B","jetpack-related-posts":[],"jetpack_likes_enabled":true,"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/posts\/37"}],"collection":[{"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/comments?post=37"}],"version-history":[{"count":0,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/posts\/37\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/media\/1045"}],"wp:attachment":[{"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/media?parent=37"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/categories?post=37"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/tags?post=37"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
What is wrong with the above code?<\/h3>\n
\nBased on the database schema shown earlier, we can see that the job-id column of the Person table can contain null values. However, in the above coding, we are using the \nThere are two ways to detect whether a null value is read.\n<\/p>\n \n1) Use the \n2) Use the Content is king and large software cannot get away from serving content. In this post, I shall document how to use Java to access relational databases, with the MySQL database as an example. <\/p>\n","protected":false},"author":1,"featured_media":1045,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"footnotes":""},"categories":[375],"tags":[6,10,9],"jetpack_featured_media_url":"https:\/\/www.techcoil.com\/blog\/wp-content\/uploads\/java-logo.jpg","jetpack_shortlink":"https:\/\/wp.me\/p245TQ-B","jetpack-related-posts":[],"jetpack_likes_enabled":true,"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/posts\/37"}],"collection":[{"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/comments?post=37"}],"version-history":[{"count":0,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/posts\/37\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/media\/1045"}],"wp:attachment":[{"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/media?parent=37"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/categories?post=37"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.techcoil.com\/blog\/wp-json\/wp\/v2\/tags?post=37"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}getInt()<\/code> method of the ResultSet<\/code> class to retrieve a the job-id value. The int data type being one of Java's primitive types is not able to store the null. On my machine, the getInt()<\/code> method returns 0 when it hits a null on the job-id.\n<\/p>\nHow to solve the problem?<\/h3>\n
wasNull()<\/code> method provided by the ResultSet class.\n<\/p>\n\r\n\/\/ Traverse sqlResult\r\nwhile(sqlResult.next()) {\r\n \/\/ Get the value of job-id\r\n int jobId = sqlResult.getInt("job-id");\r\n \/\/ if jobId is supposed to be null\r\n if (sqlResult.wasNull()) {\r\n System.out.println("Job ID: null");\r\n } else {\r\n System.out.println("Job ID: " + jobId);\r\n }\r\n} \/\/ end while\r\n<\/pre>\ngetObject()<\/code> method instead of the getInt()<\/code> method to retrieve the value of the job-id column. By using the getObject()<\/code> method, we are able to get null values if there any appears.\n<\/p>\n\r\n\/\/ Traverse sqlResult\r\nwhile(sqlResult.next()) {\r\n Object jobId = sqlResult.getObject("job-id");\r\n if (jobId == null) {\r\n System.out.println("Job ID: null");\r\n } else {\r\n System.out.println("Job ID: " + jobId);\r\n } \/\/ end if (jobId == null)\r\n} \/\/ end while\r\n<\/pre>\n\n \n
Share\n <\/a>\n <\/li>\n Tweet\n <\/a>\n <\/li>\n Share\n <\/a>\n <\/li>\n Save\n <\/a>\n <\/li>\n <\/ul>\n ","protected":false},"excerpt":{"rendered":"