The following question is already answered and I leave the code for someone else to work with her. This is a code to connect a java program to a MySQL database to save, edit, delete and more things to the table in the database.
I have it in 4 files so I'll put them one under the other
This file is called Final 10 project
package proyectofinal1.pkg0;
import javax.swing.JFrame;
/**
*
* @author BEEO
*/
public class Proyectofinal10 {
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
// TODO code application logic here
formulario2 form= new formulario2();
form.setVisible(true);
}
}
This is the file of the connection to the database
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
public class conexion{
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String USER = "root";
private static final String PASS = "102030";
private static final String URL = "jdbc:mysql://localhost:3306/contactos?serverTimezone=UTC&useSSL=false";
private Connection CN;
public conexion(){
CN = null;
}
public Connection getConnection(){
try {
Class.forName(DRIVER);
CN = DriverManager.getConnection(URL, USER, PASS);
} catch (ClassNotFoundException | SQLException ex) {
Logger.getLogger(conexion.class.getName()).log(Level.SEVERE, null, ex);
JOptionPane.showMessageDialog(null, ex.getMessage(), "Error al conectar al servidor", JOptionPane.ERROR_MESSAGE);
}
return CN;
}
public void close(){
try{
CN.close();
}catch(SQLException ex){
JOptionPane.showMessageDialog(null,ex.getMessage(), "Error al cerrar la conexion con la base de datos", JOptionPane.ERROR_MESSAGE);
}
}
}
This other file is the one that contains the gui of the application, that is, where the buttons are, text field, etc, what the user uses the program
I had to erase most of the netbeans generated since the web only lets me enter 30,000 characters
import java.awt.event.KeyEvent;
import java.io.BufferedReader;
import java.io.File;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.filechooser.FileNameExtensionFilter;
public class formulario2 extends javax.swing.JFrame {
private final formularioFuncion FF;
int num;
public formulario2() {
initComponents();
FF= new formularioFuncion();
verContactos();
this.setLocationRelativeTo(null);
}
private void verContactos(){
jtb_bd.setModel(FF.extraerDatos());
}
private void limpiarTF(){
jtf_id.setText("");
jtf_nombre.setText("");
jtf_apellido.setText("");
jtf_compañia.setText("");
jtf_posicion.setText("");
jtf_email.setText("");
jtf_telefono.setText("");
jta_notas.setText("");
jtf_nombre.requestFocus();
}
private void editar(){
String id = jtf_id.getText();
String nom = jtf_nombre.getText();
String apell = jtf_apellido.getText();
String com = jtf_compañia.getText();
String pos = jtf_posicion.getText();
String emai = jtf_email.getText();
String tele = jtf_telefono.getText();
String not = jta_notas.getText();
//String fot = jtf_nombre.getText();
if (num == 0){
int resp = FF.insertDatos(nom, apell, com, pos, emai, tele, not);
if (resp > 0){
verContactos();
limpiarTF();
}
}else{
int resp = FF.editarDatos(id, nom, apell, com, pos, emai, tele, not);
if (resp > 0){
verContactos();
limpiarTF();
num=0;
}
}
}
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(jbt_guardar)
.addComponent(jbt_eliminar)
.addComponent(jbt_cargar_contactos)
.addComponent(jbt_seleccionar_csv))
.addGap(19, 19, 19))
);
pack();
}// </editor-fold>
private void jtf_compañiaActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
}
private void jtf_telefonoActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
}
private void jtf_idActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
}
private void jbt_guardarActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
editar();
/* String nom = jtf_nombre.getText();
String apell = jtf_apellido.getText();
String com = jtf_compañia.getText();
String pos = jtf_posicion.getText();
String emai = jtf_email.getText();
String tele = jtf_telefono.getText();
String not = jta_notas.getText();
//String fot = jtf_nombre.getText();
int resp = FF.insertDatos(nom, apell, com, pos, emai, tele, not);
if (resp > 0){
verContactos();
limpiarTF();
}*/
}
private void jtf_filtroActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
}
private void jtf_filtroKeyReleased(java.awt.event.KeyEvent evt) {
char tecla = evt.getKeyChar();
if(tecla == KeyEvent.VK_ENTER ){
jtb_bd.setModel(FF.filtro(jcb_filtro.getSelectedIndex(), jtf_filtro.getText()));
}
}
private void jtb_bdMouseClicked(java.awt.event.MouseEvent evt) {
int row = jtb_bd.getSelectedRow();
jtf_id.setText(jtb_bd.getValueAt(row, 0).toString());
jtf_nombre.setText(jtb_bd.getValueAt(row, 1).toString());
jtf_apellido.setText(jtb_bd.getValueAt(row, 2).toString());
jtf_compañia.setText(jtb_bd.getValueAt(row, 3).toString());
jtf_posicion.setText(jtb_bd.getValueAt(row, 4).toString());
jtf_email.setText(jtb_bd.getValueAt(row, 5).toString());
jtf_telefono.setText(jtb_bd.getValueAt(row, 6).toString());
jta_notas.setText(jtb_bd.getValueAt(row, 7).toString());
//jtf_foto.setText(jtb_bd.getValueAt(row, 8).toString());
String estado = jtb_bd.getValueAt(row, 6).toString();
if (estado.equals("5")){
}
num = 1;
}
private void jbt_eliminarActionPerformed(java.awt.event.ActionEvent evt) {
int fila = jtb_bd.getSelectedRowCount();
if (fila < 1){
JOptionPane.showMessageDialog(null, "Seleccione un registro de la tabla");
}else{
if (FF.eliminar(jtb_bd.getValueAt(jtb_bd.getSelectedRow(), 0).toString()) > 0);
limpiarTF();
verContactos();
}
}
private void jbt_abrir_imagenActionPerformed(java.awt.event.ActionEvent evt) {
JFileChooser dato= new JFileChooser();
int op = dato.showOpenDialog(this);
if(op==JFileChooser.APPROVE_OPTION){
// jTextField1.setText(dato.getSelectedFile().getPath());
}
}
private void jbt_cargar_contactosActionPerformed(java.awt.event.ActionEvent evt) {
}
private void jbt_seleccionar_csvActionPerformed(java.awt.event.ActionEvent evt) {
JFileChooser seleccionar = new JFileChooser();
FileNameExtensionFilter filtro = new FileNameExtensionFilter("*.csv", "csv");
seleccionar.setFileFilter(filtro);
seleccionar.showOpenDialog(null);
File f = seleccionar.getSelectedFile();
//fileName = f.getAbsolutePath();
/*int seleccion = seleccionar.showOpenDialog(this);
//Si el usuario, pincha en aceptar
if (seleccion == JFileChooser.APPROVE_OPTION) {
//Seleccionamos el fichero
fichero = file.getSelectedFile();
final JFileChooser elegirImagen = new JFileChooser();
elegirImagen.setMultiSelectionEnabled(false);
int o = elegirImagen.showOpenDialog(this);*/
}
}
This last file contains the functions of the buttons
import java.io.BufferedReader;
import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;
import proyectofinal1.pkg0.conexion;
public class formularioFuncion {
private final String QUERY_INSERT = "insert into cliente(nombre, apellido, compañia, posicion, email, telefono, notas) values(?,?,?,?,?,?,?)";
private final String QUERY_SELECT = "select * from cliente";
private PreparedStatement PS;
private DefaultTableModel DT;
private ResultSet RS;
private conexion CN;
public formularioFuncion(){
PS = null;
CN = new conexion();
}
private DefaultTableModel columDB(){
DT = new DefaultTableModel();
DT.addColumn("id");
DT.addColumn("nombre");
DT.addColumn("apellido");
DT.addColumn("compañia");
DT.addColumn("posicion");
DT.addColumn("email");
DT.addColumn("telefono");
DT.addColumn("notas");
//DT.addColumn("foto");
return DT;
}
public int insertDatos(String nom,String apell,String com,String pos,String emai,String tele,String not){
int res = 0;
try{
PS = CN.getConnection().prepareStatement(QUERY_INSERT);
PS.setString(1, nom);
PS.setString(2, apell);
PS.setString(3, com);
PS.setString(4, pos);
PS.setString(5, emai);
PS.setString(6, tele);
PS.setString(7, not);
//PS.setString(8, fot);
res =PS.executeUpdate();
if(res > 0){
JOptionPane.showMessageDialog(null, "Registro guardado exitosamente");
}
}catch(Exception e){
System.out.println("Error al guardar los datos en la BD" + e.getMessage());
}finally{
PS=null;
CN.close();
}
return res;
}
public int editarDatos(String id,String nom,String apell,String com,String pos,String emai,String tele,String not){
String SQL = "update cliente set nombre='"+nom+"', apellido='"+apell+"', compañia='"+com
+"', posicion='"+pos+"', email='"+emai+"', telefono='"+tele+"', notas='"+not+"' where id="+id;
int res = 0;
try{
PS = CN.getConnection().prepareStatement(SQL);
res =PS.executeUpdate();
if(res > 0){
JOptionPane.showMessageDialog(null, "Registro editado exitosamente");
}
}catch(Exception e){
System.out.println("Error al editar los datos en la BD" + e.getMessage());
}finally{
PS=null;
CN.close();
}
return res;
}
public int eliminar(String id){
String SQL = "delete from cliente where id = " + id;
int res = 0;
try{
PS = CN.getConnection().prepareStatement(SQL);
res =PS.executeUpdate();
if(res > 0){
JOptionPane.showMessageDialog(null, "Registro eliminado exitosamente");
}
}catch(Exception e){
System.out.println("Error al eliminar los datos en la BD" + e.getMessage());
}finally{
PS=null;
CN.close();
}
return res;
}
public DefaultTableModel extraerDatos(){
try{
columDB();
PS = CN.getConnection().prepareStatement(QUERY_SELECT);
RS = PS.executeQuery();
Object[] fila=new Object[8];
while(RS.next()){
fila[0]=RS.getInt(1);
fila[1]=RS.getString(2);
fila[2]=RS.getString(3);
fila[3]=RS.getString(4);
fila[4]=RS.getString(5);
fila[5]=RS.getString(6);
fila[6]=RS.getString(7);
fila[7]=RS.getString(8);
DT.addRow(fila);
}
}catch(SQLException e){
System.out.println("Error al extraer los datos de la BD" + e.getMessage());
}finally{
PS = null;
RS = null;
CN.close();
}
return DT;
}
public DefaultTableModel filtro(int crt, String info){
String SQL = null;
if(crt == 0){
SQL = "select * from cliente where nombre like '" + info + "%'";
}else if(crt == 1){
SQL = "select * from cliente where apellido like '" + info + "%'";
}else if(crt == 2){
SQL = "select * from cliente where compañia like '" + info + "%'";
}else if(crt == 3){
SQL = "select * from cliente where telefono like '" + info + "%'";
}
try{
columDB();
PS = CN.getConnection().prepareStatement(SQL);
RS = PS.executeQuery();
Object[] fila=new Object[8];
while(RS.next()){
fila[0]=RS.getInt(1);
fila[1]=RS.getString(2);
fila[2]=RS.getString(3);
fila[3]=RS.getString(4);
fila[4]=RS.getString(5);
fila[5]=RS.getString(6);
fila[6]=RS.getString(7);
fila[7]=RS.getString(8);
DT.addRow(fila);
}
}catch(SQLException e){
System.out.println("Error al extraer los datos de la BD" + e.getMessage());
}finally{
PS = null;
RS = null;
CN.close();
}
return DT;
}
/*private void importar(File a){
try{
BufferedReader br = new BufferedReader(new FileReader(filename1));
String line;
while((line = br.readLine()) !=null){
String[]value = line.split(",");
String SQL = "insert into cliente(nombre, apellido, compañia, posicion, email, telefono, notas)" +
"values('"+value[0]+"', '"+value[1]+"', '"+value[2]+"', '"+value[3]+"', '"+value[4]+"', '"+value[5]+"', '"+value[6]+"'";
PS = CN.getConnection().prepareStatement(SQL);
RS = PS.executeQuery();
}
br.close();
}catch(Exception e){
JOptionPane.showMessageDialog(null, e);
System.out.println("Error al importar los datos en la BD" + e.getMessage());
}
}*/
}