JDBC: Creating a Database Table

The primary goal of using the JDBC API is to manipulate data contained in tables in a database. We can managedata in tables using the SQL statements SELECT, INSERT, UPDATE, and DELETE, which use table names directly.Sometimes we cannot refer to the table names in our JDBC calls directly. Instead, we can execute a storedprocedure using the JDBC API, and the stored procedure uses table names. One way or the other, we end up usingtables when we work with JDBC.

Column Name Data Type Length Null Value Allowed Comments
person_id integer No Primary Key
first_name string 20 No
last_name string 20 No
gender string 1 No
Dob Date Yes
income double Yes

The data types of columns shown in this table are generic. We need to use data types specific to ourDBMS. For example, for the first_namecolumn, we can use the data type of varchar2(20)in the Oracle databaseand varchar(20)in the SQL Server database. Similarly, for the person_idcolumn, you can use a data type ofnumber(8, 0)in the Oracle database and intin the SQL Server database.

Every DBMS provides a tool, either character-based, graphical, or both, that lets us work with database objectssuch as tables, stored procedures, functions, etc. For example, we can use the Oracle SQL*PLUS tool for Oracle DBMSfrom Oracle, the SQL Server Management Studio tool for SQL Server DBMS from Microsoft, the Interactive SQL toolfor Adaptive Server Anywhere (ASA) from Sybase, etc.

Oracle Database
	create table person (
	person_id number(8,0) not null,
	first_name varchar2(20) not null,
	last_name varchar2(20) not null,
	gender char(1) not null,
	dob date,
	income number(10,2),
	constraintpk_person primary key(person_id)
	);
	
Adaptive Server Anywhere Database
	create table person (
	person_id integer not null default null,
	first_namevarchar(20) not null default null,
	last_namevarchar(20) not null default null,
	gender char(1) not null default null,
	dob date null default null,
	income double null default null,
	primary key (person_id)
	);
	
SQL Server Database
	create table person (
	person_idint NOT NULL,
	first_namevarchar(20) NOT NULL,
	last_namevarchar(20) NOT NULL,
	gender char(1) NOT NULL,
	dobdatetime NULL,
	income decimal(10,2) NULL,
	constraintpk_person primary key (person_id)
	);
	
DB2 Database
	create table person (
	person_id integer not null,
	last_namevarchar(20) not null,
	gender character (1) not null,
	dob date,
	income double,
	constraintpk_person_id primary key (person_id)
	);
	
MySQL Database
	create table person (
	person_id integer not null primary key,
	first_namevarchar(20) not null,
	last_namevarchar(20) not null,
	gender char(1) not null,
	dobdatetime null,
	income double null
	);
	
Java DB Database
	create table person (
	person_id integer not null,
	first_namevarchar(20) not null,
	last_namevarchar(20) not null,
	gender char(1) not null,
	dob date,
	income double,
	primary key(person_id)
	);
	
Libraries Used in Java DB Server and Client Applications
Library Name Description
derby.jar Contains the Java DB database engine code. Used for Java DB runningin embedded mode. For Java DB running in server mode, it is needed on the server.
derbytools.jar Required for running all Java DB tools such as ij, dblook, etc.
derbyrun.jar An executable JAR file used to start Java DB tools. Including this file in the CLASSPATH alsoincludes derby.jar, derbyclient.jar, derbytools.jar, derbynet.jar files in the CLASSPATH.
derbynet.jar Contains the Java DB Network Server code. It is required to start the Java DB Network Server.
derbyclient.jar Contains the Java DB Network Client JDBC driver. It is required for a Java application toconnect to a Java DB server over a network.

Copyright 1999- Ducat Creative, All rights reserved.