 |
s8361: Java: User Exp-JDBC + MySQL.
|
 |
MySQL and JDBC were combined with Java Swing components to produce a complete conference registration system, including super-mail-merge like reporting capability. The project was developed by combining mainframe DB2 experience with more recent Java skills to develop a working application for the Windows platform that is 100% open-source. That was important for the non-profit organization for which this system was targeted. Come learn what the author calls "just-in-time" programming. The full HTML version of this presentation with links to the source code and demo files can be found at www.jsrsys.com.
The author has been writing programs since 1965 in Fortran, COBOL, PL/1, Algol, Pascal, C, C++, and various assembly languages. He has been exploring Java since 1996.
JSR Systems (SHARE Code: JSR)
2805 Robbs Run
Austin, Texas 78703
sryder@jsrsys.com
www.jsrsys.com
512.917.0823
| The University of Texas at Austin | 1965-1966 |
| Texas Education Agency | 1966-1995++ |
| Houston Ind. School District | 1975-1981 |
| Conroe Ind. School District | 1980-1993 |
| United States Navy | 1966-1994 |
| Capitol Appraisal Group, Inc. | 1981-... |
Return to JSR Systems Services.
Return to JSR Systems' home page.
Copyright © 2003, JSR Systems, All rights reserved.
Navigation hints:
- Clicking on SHARE logo will go back one "page".
- Clicking on SHARE moto will go forwared one "page".
- Clicking anywhere else on page will go to "next page".
Table of Contents
- Introduction
- MySQL and Demo
- DataReport class
- DataUpdate class
- DataLoad class
- JsrSQL class
- Programs (Objects/Classes) Used Includes link to zip file of all source...
- Helpful Books
Introduction:
- Goal was to create registration system using 100% free components.
- Java: because I like it and it is free.
- MySQL: because it scales and it is free.
- System components include: Maintnance (use MySQL commands + some Java), Update (Java) and Report (Java)
- Used successfully for two events, one with 124 participants.
MySQL
- Article from www.ExtremeTech.com:
- Obtained MySQL and JDBC from MySQL.com
used default C:\MySQL for sql code.
copy mysql-connector-java-2.0.14-bin.jar file to ...lib\ext. - Did Articles: Result was Describe.java
- JsrSQL: cloned all methods from Describe, add new methods as needed "just in time"
- Used Run command with *.txt files for input to create DB
- DataUnload: create INSERT statements (Load Data not supported on Win98).
- Serves as backup, and facilitates changes to database: modify DBcreate.txt, then reload.
- System is still a work in progress, different sections (update, report) done as BAT files.
- Will eventually wrap it all together with one super GUI that does it all,
probably by just adding a menu bar to the Update program. - Demo: KB3create.txt, KB3load...txt, Unload, reload.
DataReport class
- Pseudo Mailmerge: actually working before DataUpdate
- Assumes anyting inside brackets [...] is a command
- Examples: [*begin] [*next] [*end]
- [xyz] implies getString(xyz)
- DataReport.txt defines each report title and select statement parameters
- Input files can be anything, rtf, htm, csv, text...
- Can use Notepad to edit or insert [...] commands. Does NOT support doc files.
- It is a bit tricky to insert [*begin] and [*end] commands into RTF files.
Have to be careful about matching {...} braces. - Unique Feature: 3 column logic...
- Demo: DataReport.txt, TeamMember.htm (3 up example).
DataUpdate class
- Only parameter is DataBaseName, everything else derived from database itself.
- Assumes Who is main table, common key is WhoID.
- Assumes second element is always LastUpdate (set to *new* to indicate new row).
- Assumes all tables have a WhoID.
- Note: does not yet support 1 to many or many to 1, assumes 1 to 1.
This would be an issue for the collection portion if allow one check to pay for many persons.
Workaround is to split one check manuually, and have separate deposit, balance, etc. for multiple payments.
DataLoad class
- Input is table=tablename, followed by elementName=elementValue
- In actual use, I wrote a progam to extract FormMail generated email, creating input file.
- Thus, on-line registration was available with very little manual processing.
User fills out form which is emailed to me.
I do one file/saveAs for each email.
Run the extract program (tailored for a specific registration).
Run the DataLoad program to add new registrations to database. - Future extension: a Java based Mail Reader that reads email from a spcecific email ID, saves all email files, then invoke the converter
JsrSQL class
- Typical calling sequence:
creation typically creates connection
setSelect(...) defines select parameters, returns count of rows
getColNames() returns name of each column/field
setNextRow() for each row
getRowValues() returns an array of all values for current row
update(...) will update current row, if ...[1] == *new* will add new row
delete() will delete current row
- Also supports getString(...) to get value for specific column by name (used by DataReport)
- There are other methods (getTables(...), getFields(...), getNames(...) that facilitate letting the database itself describe itself to invoking objects/programs.
| Constructor Summary | |
JsrSQL()
null constructor: requires user to do setDataBaseName |
|
JsrSQL(java.lang.String dbName)
constructor w/ dataBaseName only needs to be suppied ONCE per class instances share connection Normal Use: * One (1) instance per open table * Works with an array of rowValues, one per Column/Field * setSelect(...everything after SELECT *) * getColNames() // once after the SELECT (can be from multiple tables)... |
|
| Method Summary | |
void |
close()
closes results, stmt, conn. |
java.lang.String |
delete(java.lang.String[] newRowValue)
delete current Row |
int[] |
getColDisplaySize()
return all Display Sizes for most recent setSelect |
java.lang.String[] |
getColNames()
return all Column Names for most recent setSelect |
int |
getFields(java.lang.String tableName)
returns int # of fields (columns) for current table |
java.lang.String[] |
getNames()
returns String[] of all field (column) names for current table (set by getFields). |
java.lang.String[] |
getRowValues()
returns String[] of all field (column) values for current row. |
java.lang.String |
getString(int colNum)
returns String value for colNum |
java.lang.String |
getString(java.lang.String colName)
return String value for colName |
java.lang.String[] |
getTables(java.lang.String dbName)
return String[] of all tables in this database Windows MySQL returns all lower-case, change to Xxxxx (toUpperCase on 1st digit). |
void |
setDataBaseName(java.lang.String dbName)
supply dataBaseName via call here, or constructor |
boolean |
setNextRow()
moves cursor to next row, return false if no more rows. |
int |
setSelect(java.lang.String fromName)
executes Query (fromName), return COUNT(*). |
java.lang.String |
update(java.lang.String[] newRowValue)
reverse of getRowValues(): assumes that newRowValue "matches" rowValue[] * assumes: [0] == WhoID, [1] == LastUpdate. |
Programs (Classes/Object) Used:
- Zip file with all source, sample reports, and actual files used in demo.
- JsrLineIn--Reader Class (encapsulates read logic text in)
- JsrLineOut--Writer Class (encapsulates write text out)
- JsrSortByKey--Sort By Key Class
- JsrSysout--Display/Accept Class
- JsrUtil--Padding and date support Class
- JsrRTF--Convert a file to Rich Text Format
- JsrRunExec--similar to C system command, used to issue start [fileName]
this works on Win98. Unfortunately on WinNT/XP:
the command must be "cmd /C start [filename].
because start is in command shell.
Workaround: do RunStart.bat [filename] and then tailor the bat file.
Better, if command is start and System.getProperty("os.name")
then insert cmd /c if not Win 98.
Books that were helpful:
"Java in 21 Days " by Laura Lemay (SAMS)
"Java in a Nutshell" by David Flanagan (O'Reilly)
"Java SWING " by Robert Eckstein, Marc Loy & Dave Wood (O'Reilly)
"Java 2D Graphics" by Jonathan Knudsen (O'Reilly)
Return to Table of Contents