|
|
||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||
|
In the instructions below, substitute your UW Net ID wherever you see "uwnetid". Last updated: 12 Sep 2007
JDBC for MySQLJDBC is the Java Database Connectivity API, which allows a Java program to access data stored in tabular form, such as those in relational databases and spreadsheets. Typically, a database driver is written by the database vendor and it is implemented as a class which is loaded prior to using the JDBC methods it provides. The driver and supporting classes and information are typically packaged in a jar file. MySQL is a popular open-source database management system (DBMS) that supports a wide variety of APIs and means of interfacing. JDBC is one way to access a mySQL database, and the official JDBC driver for MySQL is called MySQL Connector/J. It is a "type 4" driver -- a "native-protocol pure Java driver". A good source for documentation -- though a bit dated -- on how to use this driver can be found here. In the section on "Establishing a Connection", for this line: Class.forName("org.gjt.mm.mysql.Driver").newInstance();substitute this line: Class.forName("com.mysql.jdbc.Driver").newInstance();
JDBC Drivers in the LabsJDBC drivers for some DBMSes have been installed in the labs for your convenience. If they are not in a CLASSPATH environment variable, you will need to either set one or specify all classpaths as a parameter to the java command when running your application. For MySQL:
The Connection StringThe driver documentation lists how to register the driver with the driver manager , which effectively loads the driver so you can create a connection to the database that the driver supports. For this line: Class.forName("org.gjt.mm.mysql.Driver").newInstance();substitute this line: Class.forName("com.mysql.jdbc.Driver").newInstance();to use Connector/J. A "connection string" is a URI for specifying how to access the database. The format for the connection string is well defined and in that documentation . However, various values are specific to the installation. An empty MySQL database has already been created for you on the Repository Server. Specifically, the connection string requires the host, port, database, database user, and database user password:
...and the restThe rest of the code is specific to the JDBC API, your database and table definitions, and the MySQL command set.
Importing Text FilesDue to security concerns, mysql's FILE privilege (which enables LOAD FILE) is not granted to users. This is often used to load a file of text into a SQL table. However, similar functionality is available on the Repository Server via the gen_insert script. gen_insert is a Perl script that generates SQL INSERT statements from either comma-delimited or tab-delimited text files (e.g., exported from an Excel spreadsheet or another database). gen_insert requires a table name and the delimited file name. It does not attempt to validate the table name; this makes it independent of any particular database. If an option states that the first line is row of delimited column names, it will use those names in the INSERT statement, which forces the order of the VALUES() to be the order in which the column names are specified. This is much more accurate and flexible than simply listing the values and hoping no one changed the order of the column names. Input values from the file may be converted to conform to SQL rules in an INSERT statement as follows:
gen_insert is in the path of all Repository Server accounts. To see how to use it, enter: gen_insert For example, gen_insert can be used as follows (for example table "people" and delimited file "myfolks.txt", which does not have a row of names as the first line): gen_insert people myfolks.txt >myfolks.sql Redirection of the output (>myfolks.sql) is used here to name and save the output. As with any automatic conversion, you may encounter situations where something did not convert as expected. One source of errors would be when some values of a column contain decimal numbers and others in that column contain text; presuming the column represents text, the numbers would never be quoted and would probably cause an error when inserting. Always review the values in the output file before submitting the file to the DBMS. Within a DBMS's command shell, one can import the SQL statements. For example, from mysql: source myfolks.sql; would execute all of the INSERT statements in myfolks.sql as if the user typed them in one by one, thereby populating the "people" table with the data from the myfolks.txt file. Note that some DBMSes require that you commit the insertions made to the table before you can do anything with the new data.
Change Log
Hours | Support Information | News | Policies | Emergencies |