Thread: Retrieving data from backend

Hello,cud anyone suggest ways to extract data from a table in the backend(in my case Mysql server)and display the needed fields in a text area withiun a java frame

This would be a good place to start:
MySQL :: Using MySQL With Java

Thanks for the link! but i wanted to specifically know how to do it as an 'actionPerformed' for a JMenuItem selected...

i have this Java code that inserts and deletes data from backend i.e. MySql..
U can use either JDBC or ODBC driver...the code has both the statements..

import java.awt.*;

import java.awt.event.*;

import javax.swing.*;

import java.lang.StringBuffer;

import java.io.IOException;

import java.io.*;

import java.util.*;

import java.io.File;

import javax.xml.parsers.DocumentBuilder;

import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;

import org.w3c.dom.Element;

import org.w3c.dom.Node;

import org.w3c.dom.NodeList;

import java.awt.*;

import java.awt.event.*;

import java.text.Format.Field;

import java.sql.*;

import java.sql.SQLException;

import java.util.ResourceBundle;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.sql.DataSource;



//******************

import java.sql.*; //allows sql calls to the database



public class AddressBookMySql extends JFrame

{

private DataPanel myDataPanel;

private Connection dbconn=null;

//Connection con = null;

int numberPeople=0;

//private static int numPeople=0;

private static String info;

private static JTextArea txtInfo=new JTextArea( 8, 40 ); //8 rows 40 cols needs to be here to speak

//across classes

public AddressBookMySql()

{

super("This is my Phone Book which calls a database, La La La");

GridLayout myGridLayout= new GridLayout(3,1); //3 rows 1 col allows 3 panels

Container p = getContentPane();

myDataPanel=new DataPanel();

p.add(myDataPanel);

myDataPanel.setLayout(myGridLayout);

//Statement stmt;

//Statement stmt2;



//ResultSet rs;

//ResultSet rs2;

// Define URL of database server for

// database named 'user' on the faure.

String url;

//*********************************

try

{

System.out.println("bang");

//url = "jdbc:mysql://localhost/test";

url = "jdbcdbc:myaddressbook4";

//Class.forName ("com.mysql.jdbc.Driver").newInstance ();

//url = "jdbc:mysql://localhost//myaddressbook4";



Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );

dbconn = DriverManager.getConnection (url, "root", "student");

System.out.println("bang");





//url ="jdbc:mysql://localhost/" +"test"+ "?user=root&password=" +"student"+ "&useUnicode=ue&characterEncoding=gb2312"; //original

dbconn = DriverManager.getConnection( url );

info="Connection successful\n";

}

catch ( ClassNotFoundException cnfex ) //yes 3 catches

{

cnfex.printStackTrace();

info=info+"Connection unsuccessful\n" + cnfex.toString();

}

catch ( SQLException sqlex )

{

sqlex.printStackTrace();

info=info+"Connection unsuccessful\n" +sqlex.toString();

}

catch ( Exception excp )

{

excp.printStackTrace();

info=info+excp.toString();

}

//**********************************



txtInfo.setText(info); //sets connection information

setSize(500,290);

setVisible(true);

}

public static void main(String args[])

{

AddressBookMySql myAddressBookMySql= new AddressBookMySql();

myAddressBookMySql.addWindowListener

(

new WindowAdapter()

{

public void windowClosing(WindowEvent e)

{

System.exit(0);

}

}

);

}

//*******************************

class DataPanel extends JPanel implements ActionListener

{

JLabel lblIDCap= new JLabel("Record Number");

JLabel lblLast=new JLabel("Last Name");

JLabel lblFirst=new JLabel("First Name");

JLabel lblPhone=new JLabel("Phone Number");

//JTextArea txtInfo=new JTextArea();

JLabel lblID=new JLabel(" "); //10 spaces

JTextField txtLast=new JTextField(10);

JTextField txtFirst=new JTextField(10);

JTextField txtPhone=new JTextField(10);



JButton btnAdd=new JButton("Add Record");

JButton btnFind=new JButton("Find Record");

JButton btnDelete=new JButton("Delete Record");

JButton btnUpdate=new JButton("Update Record"); //**

JButton btnClear=new JButton("Clear");

JButton btnExit=new JButton("Exit");



public DataPanel()

{

JPanel myPanel = new JPanel();

JPanel myPanel2 = new JPanel();

JPanel myPanel3 =new JPanel(); //**

myPanel.setLayout(new GridLayout (4,2)); //4 rows 2 cols

myPanel2.setLayout(new GridLayout (2,3)); //2 rows 3 cols

myPanel3.setLayout(new GridLayout(1,1)); //1 row 1 col

add(myPanel);

add(myPanel2);

add(myPanel3); //**

myPanel.add(lblIDCap);

myPanel.add(lblID);

myPanel.add(lblLast);

myPanel.add(txtLast);

myPanel.add(lblFirst);

myPanel.add(txtFirst);

myPanel.add(lblPhone);

myPanel.add(txtPhone);

myPanel2.add(btnAdd);

myPanel2.add(btnFind);

myPanel2.add(btnDelete);

myPanel2.add(btnUpdate);

myPanel2.add(btnClear);

myPanel2.add(btnExit);

myPanel3.add( new JScrollPane(txtInfo)); //**

//puts txtInfo on application and allows it to scroll

btnAdd.addActionListener(this);

btnFind.addActionListener(this);

btnUpdate.addActionListener(this);

btnClear.addActionListener(this);

btnExit.addActionListener(this);

btnDelete.addActionListener(this);

}

public void actionPerformed(ActionEvent event)

{

String ID=""; //must initialize to ""

String Last="";

String First="";

String Phone="";

Object source=event.getSource();

ID=lblID.getText().trim();

lblID.setText(ID);

Last=txtLast.getText().trim(); //removes additional characters

txtLast.setText(Last); //sets fields in their places

First=txtFirst.getText().trim();

txtFirst.setText(First);

Phone=txtPhone.getText().trim();

txtPhone.setText(Phone);

if (source.equals(btnAdd))

{

//********************************

try {

Statement statement = dbconn.createStatement();

if ( !Last.equals( "" ) &&

!First.equals( "" ) &&

!Phone.equals("") )

{

String temp = "INSERT INTO phonelistTable (" +

"Last, First, " +

"Phone" +

") VALUES ('" +

Last + "', '" +

First + "', '" +

Phone + "')";

txtInfo.append( "\nInserting: " +

dbconn.nativeSQL( temp ) + "\n" );

int result = statement.executeUpdate( temp );

if ( result == 1 )

{ //confirming insertion

//txtInfo.append("\nInsertion successful\n");

String query="";

try

{

query = "SELECT * FROM phonelistTable WHERE First='" +

First + "' AND Last= '" + Last + "'";

ResultSet rs = statement.executeQuery( query );

rs.next();

lblID.setText(String.valueOf(rs.getInt(1)));

}

catch ( SQLException sqlex )

{

txtInfo.append( sqlex.toString() );

}

}

else

{

txtInfo.append( "\nInsertion failed\n" );

txtFirst.setText( "" );

txtLast.setText( "" );

txtPhone.setText( "" );

}

}

else

txtInfo.append( "\nEnter last, first, and " +

"phone, then press Add\n" );

statement.close();

}

catch ( SQLException sqlex )

{

txtInfo.append( sqlex.toString() );

txtFirst.setText("Entry already exists -- re-enter");

}

}

//****************************

if (source.equals(btnFind))

{

try

{

if ( !Last.equals("") && !First.equals(""))

{

System.out.println("First="+First+ " Last="+Last);

//try

//{

Statement statement =dbconn.createStatement();

//}

//Statement statement =dbconn.createStatement();

String query = "SELECT * FROM phonelistTable WHERE First='" +

First + "' AND Last= '" + Last + "'";

//ResultSet rs = statement.executeQuery( query );

txtInfo.append( "\nSending query: " +

dbconn.nativeSQL( query ) + "\n" );

//lblID.setText(String.valueOf(rs.getInt(1)));

//ResultSet rs = statement.executeQuery( query );

ResultSet rs = statement.executeQuery( query );

display( rs );

//display( rs );

statement.close();

/*

Statement statement =dbconn.createStatement();

String query = "SELECT * FROM phonelistTable " +

"WHERE First = '" +

First + "' AND Last = '" +

Last + "';";

txtInfo.append( "\nSending query: " +

dbconn.nativeSQL( query ) + "\n" );

ResultSet rs = statement.executeQuery( query );

display( rs );

statement.close();

*/

}

else

txtLast.setText("Enter last name and First name"+

" then press Find" );

}

catch ( SQLException sqlex )

{

txtInfo.append( sqlex.toString() + sqlex.getMessage() );

}

}

//******************************************

if (source.equals(btnUpdate))

{

try

{

Statement statement = dbconn.createStatement();

if ( ! lblID.getText().equals(""))

{

String temp = "UPDATE phonelistTable SET " +

"First='" + txtFirst.getText() +

"', Last='" + txtLast.getText() +

"', Phone='" + txtPhone.getText() +

"' WHERE id=" + lblID.getText();

txtInfo.append( "\nUpdating: " +

dbconn.nativeSQL( temp ) + "\n" );

int result = statement.executeUpdate( temp );

if ( result == 1 )

txtInfo.append( "\nUpdate successful\n" );

else {

txtInfo.append( "\nUpdate failed\n" );

txtFirst.setText( "" );

txtLast.setText( "" );

txtPhone.setText( "" );

}

statement.close();

}

else

txtInfo.append( "\nYou may only update an " +

"existing record. Use Find to " +

"\nlocate the record, then " +

"modify the information and " +

"\npress Update.\n" );

}

catch ( SQLException sqlex ) {

txtInfo.append( sqlex.toString() );

}

}

//********************************************

if (source.equals(btnDelete))

{

try

{

Statement statement = dbconn.createStatement();

if ( ! lblID.getText().equals(""))

{

System.out.print(lblID.getText());

String temp = "DELETE from phonelistTable " +

" WHERE id=" + lblID.getText();

txtInfo.append( "\nDeleting: " +

dbconn.nativeSQL( temp ) + "\n" );



int result = statement.executeUpdate( temp );

if ( result == 1 )

{

txtInfo.append( "\nDeletion successful\n" );

}

else

{

txtInfo.append( "\nDeletion failed\n" );

txtFirst.setText( "" );

txtLast.setText( "" );

txtPhone.setText( "" );

}

statement.close();

}

else

txtInfo.append( "\nYou may only delete an " +

"existing record. Use Find to " +

"\nlocate the record, then " +

"press delete.\n" );

}

catch ( SQLException sqlex )

{

txtInfo.append( sqlex.toString() );

}

}

//********************************************

if (source.equals(btnClear))

{

txtLast.setText("");

txtFirst.setText("");

txtPhone.setText("");

lblID.setText("");

}

//********************************************

if (source.equals(btnExit))

{

System.exit(0);

}

}

//********************************************

public void display( ResultSet rs )

{

try

{

rs.next();

int recordNumber = rs.getInt( 1 );

System.out.println("record number="+recordNumber);

lblID.setText( String.valueOf(recordNumber) );

txtLast.setText( rs.getString( 1) ); //2 is second column in database

txtFirst.setText( rs.getString( 2 ) ); //3 is third column in database

txtPhone.setText( rs.getString( 3 ) );

}

catch ( SQLException sqlex )

{

txtInfo.append( "\n*** Phone Number Not In Database ***\n" );

}

}

}

}

u have to change the statement for localhost and also the port u have installed MySql to listen..also the username and password can change according to u..

Thanks for the code.