顯示具有 mysql 標籤的文章。顯示所有文章

2017年3月23日星期四

[Node.js][mysql] How to install mysql(mariaDB)

Posted by Computer Newbie on 凌晨3:11 in mysql, Node.js / No comments

Install mysql(mariaDB) package
sudo pacman -S mysql

Add one new user was called “mysql”.
sudo useradd mysql

Change mysql’s owner
sudo chown mysql:mysql /var/lib/mysql/ -Rf

You need to initialize the MariaDB data directory prior to starting the service.
sudo mysql_install_db –user=mysql –basedir=/usr –datadir=/var/lib/mysql

Mysql(mariaDB) will create mysqld.sock under “/var/run/mysqld”, so I need to prepare right folder and right permission of folder for mysql(mariaDB).
sudo mkdir /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld/

Start mysql(mariaDB) now
sudo systemctl start mysqld

Start mysql(mariaDB) after booting.
sudo systemctl start mysqld

[Node.js][Simple Example] How to connect to mysql

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : '< MySQL username >',
  password : '< MySQL password >',
  database : '<your database name>'
});

connection.connect();

connection.query('SELECT * from < table name >', function(err, rows, fields) {
  if (!err)
    console.log('The solution is: ', rows);
  else
    console.log('Error while performing Query.');
});

connection.end();

Reference:

[mysql] How to install mysql(mariaDB)

Posted by Computer Newbie on 凌晨12:17 in mysql / No comments

Install mysql(mariaDB) package
sudo pacman -S mysql

Add one new user was called “mysql”.
sudo useradd mysql

Change mysql’s owner
sudo chown mysql:mysql /var/lib/mysql/ -Rf

You need to initialize the MariaDB data directory prior to starting the service.
sudo mysql_install_db –user=mysql –basedir=/usr –datadir=/var/lib/mysql

Mysql(mariaDB) will create mysqld.sock under “/var/run/mysqld”, so I need to prepare right folder and right permission of folder for mysql(mariaDB).
sudo mkdir /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld/

Start mysql(mariaDB) now
sudo systemctl start mysqld

Start mysql(mariaDB) after booting.
sudo systemctl enable mysqld

Reference:

[MySQL] SQL structure example

Posted by Computer Newbie on 凌晨4:19 in mysql / No comments

[MySQL] SQL structure example

-- phpMyAdmin SQL Dump

-- version 3.5.5

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Apr 07, 2014 at 06:49 AM

-- Server version: 5.5.33-31.1

-- PHP Version: 5.3.17

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

--

-- Database: `happy_student`

--

-- --------------------------------------------------------

--

-- Table structure for table `student`

--

CREATE TABLE IF NOT EXISTS `student` (

`serial` mediumint(8) NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL,

`sex` varchar(6) NOT NULL,

`year` smallint(2) NOT NULL,

`month` tinyint(3) NOT NULL,

`day` tinyint(3) NOT NULL,

`phone` varchar(20) NOT NULL,

`mail` varchar(50) NOT NULL,

`address` varchar(50) NOT NULL,

`reg_data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`serial`),

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

[NetBean][Java] Load mysql-connector-java-5.1.6.jar into BeanNet

Posted by Computer Newbie on 上午9:15 in Java, mysql, POS系統 / No comments

[NetBean][Java] Load mysql-connector-java-5.1.6.jar into BeanNet

[Purpose]

Java code connect with Mysql.

1. Download mysql-connector-java-5.1.6.jar from internet

Laod this file into NetBean.

Right click

Select java file

Then will see package into NetBean.

When save some data into Mysql will successfully.

[POS] POS learning track

Posted by Computer Newbie on 晚上7:56 in 學習區, Java, mysql, POS系統, Ubuntu / No comments

Database :

Mysql teach:

[mysql] input data to database

JAVA tutor :

Java Teach :

Java Swing Tutorials

Java mysql :

A Java MySQL INSERT example (using Statement)

NetBeans :

[java] (16) List all data in jTable from database, Edit Cell and store it

Posted by Computer Newbie on 晚上8:13 in 學習區, Java, mysql, NetBean, POS系統 / No comments

class MyTableModel extends AbstractTableModel

{

//     import java.util.*;

       private ArrayList<String> colname;

       private ArrayList<ArrayList> data;

       public MyTableModel(ResultSet rs)

       {

          try{

             // 取得欄位數量

             ResultSetMetaData rm = rs.getMetaData();

             int cnum = rm.getColumnCount();

             colname = new ArrayList<String>(cnum);

             // 取得欄位名稱

             for(int i=2; i<=cnum; i++){

                colname.add(rm.getColumnName(i));

             }

             // 取得列

             data = new ArrayList<ArrayList>();



             while(rs.next()){



                ArrayList<String> rowdata = new ArrayList<String>();

                for(int i=2; i<=cnum; i++){

                   System.out.println("happy1 int i=2; i<=cnum; i++ ");

                   rowdata.add(rs.getObject(i).toString());

                }



                data.add(rowdata);

                System.out.println("afdafads");

                System.out.println(data);

             }

           }

           catch(Exception e){

              e.printStackTrace();

          }







       }



           /*

     * Don't need to implement this method unless your table's

     * editable.

     */

      // This one can edit the cell

       public boolean isCellEditable(int row, int col) {

        //Note that the data/cell address is constant,

        //no matter where the cell appears onscreen.

            return true;



       }

   public void setValueAt(Object value, int row, int col) {



            System.out.println(value.getClass());

            System.out.println("setValueAt Row is :" + row);

            System.out.println("setValueAt col is :" + col);

            System.out.println("setValueAt :" + value);

            // After edit cell can store the data into table

            ArrayList rowdata = (ArrayList)data.get(row);

            System.out.println((ArrayList)data.get(row));

            rowdata.set(col, value);

            System.out.println(rowdata.get(col));

        }



       public int getRowCount()

       {



          System.out.println("getRowCount() data.size :"+data.size());



          return data.size();

       }

       public int getColumnCount()

       {

          System.out.println("getColumnCount() colname.size :"+colname.size());

          return colname.size();

       }

       public Object getValueAt(int row, int column)

       {

          System.out.println("getValueAt");

          ArrayList rowdata = (ArrayList)data.get(row);





          System.out.println("rowdata :" + rowdata);

          return rowdata.get(column);

       }

       public String getColumnName(int column)

       {

          System.out.println("getColumnName");

          return (String) colname.get(column);

       }

   }



    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {

        // TODO add your handling code here:

        Connection conn = null;

        String url = "jdbc:mysql://localhost:3306/sale?useUnicode=true&characterEncoding=big5";

        String user = "testuser";

        String password = "test623";

        try {

            conn = DriverManager.getConnection(url, user, password);

            Statement st = conn.createStatement();



//            String qry = "SELECT * FROM total_calc";

//            String qry = "SELECT * FROM total_calc";

              String qry = "SELECT * FROM tabaco_product order by Tabaco_order " ;

            ResultSet rs = st.executeQuery(qry);



                        if (rs.last()){

                   int i = rs.getRow();

                    System.out.println(i);





               System.out.println(rs.last());

               rs.first();



            }

            // put My TableModel(rs) into setModel

            data_jTable1.setModel(new MyTableModel(rs));



            rs.close();

            st.close();

            conn.close();



        } catch (SQLException ex) {

            System.out.println(ex.getMessage());

        }







    }

[mysql] mysqldump get database from mysql

Posted by Computer Newbie on 凌晨2:41 in 學習區, Java, mysql / No comments

1.mysqldump -u root -p --all-database > d:\XXX.sql

[java] (15) jComboBox connect to mysql

Posted by Computer Newbie on 下午2:29 in 學習區, Java, mysql, NetBean, POS系統 / No comments

public void jComboBox_connect_mysql (){



             Connection conn = null;

        String url = "jdbc:mysql://localhost:3306/sale?useUnicode=true&characterEncoding=big5";

        String user = "testuser";

        String password = "test623";

        try {

            conn = DriverManager.getConnection(url, user, password);

            Statement st = conn.createStatement();





            ResultSet rs = st.executeQuery( "SELECT * FROM tabaco_product " );



           try{

           while (rs.next()) {

                jComboBox1.addItem(rs.getString("Tabaco_name"));

           } // while

           }catch(ArrayIndexOutOfBoundsException e){}





            rs.close();





            st.close();

            conn.close();



        } catch (SQLException ex) {

            System.out.println(ex.getMessage());

        }







    }

[java] (14) Update the data of mysql with java code (NetBean )

Posted by Computer Newbie on 上午9:12 in 學習區, Java, mysql, NetBean, POS系統 / No comments

private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {

// TODO add your handling code here:

Connection conn = null;

PreparedStatement pstm = null;

String url = "jdbc:mysql://localhost:3306/sale?useUnicode=true&characterEncoding=big5";

String user = "testuser";

String password = "test623";

// Calendar date = Calendar.getInstance();

// SimpleDateFormat dateformatter = new SimpleDateFormat("yyyy-MM-dd");

try{

conn = DriverManager.getConnection(url, user, password);

Statement st = conn.createStatement();

for (int i=0 ;i
try {

String Shift_Data = Shift_jComboBox2.getSelectedItem().toString();

String Data_Year = Year_jComboBox2.getSelectedItem().toString();

String Data_Month = Month_jComboBox3.getSelectedItem().toString();

String Data_Day = Day_jComboBox4.getSelectedItem().toString();

String Date_Data = Data_Year +"-"+ Data_Month +"-"+ Data_Day;

String Goods_name_Data = jTable1.getValueAt(i,0).toString();

String Unit_price_Data = jTable1.getValueAt(i,1).toString();

String Stocks_DataC = jTable1.getValueAt(i,2).toString();

String Good_in_stock_DataD = jTable1.getValueAt(i,3).toString();

String Ship_DataE = jTable1.getValueAt(i,4).toString();

String Balance_DataF = jTable1.getValueAt(i,5).toString();

String Selling_goods_DataG = jTable1.getValueAt(i,6).toString();

String Subtotal_DataH = jTable1.getValueAt(i,7).toString();

String Space_reserve_DataI = jTable1.getValueAt(i,8).toString();

String query = "update total_calc set Stocks= ?, Good_in_stock= ?, Ship= ?, Balance= ?, Selling_goods= ?, Subtotal= ?, Space_reserve= ? where Date = ? and Shift = ? and Goods_name= ? " ;

//綠色是要變更的項目藍色是所選擇的項目選項越多越精準

// green color is the item which we want to change. Blue color is the item which we want green color //item to change. More condition is better. Between item and item use "and".

// System.out.println(Data);

pstm = conn.prepareStatement(query);

System.out.println(Date_Data);

pstm.setString(1,Stocks_DataC);//按照上面?的順序 (follow the question mark on the //above.)

pstm.setString(2,Good_in_stock_DataD);

pstm.setString(3,Ship_DataE);

pstm.setString(4,Balance_DataF);

pstm.setString(5,Selling_goods_DataG);

pstm.setString(6,Subtotal_DataH);

pstm.setString(7,Space_reserve_DataI);

pstm.setString(8, Date_Data );

pstm.setString(9,Shift_Data);

pstm.setString(10,Goods_name_Data);

System.out.println(Goods_name_Data);

System.out.println("happy");

pstm.executeUpdate();

}catch(NullPointerException e){

// jTable1.setValueAt(0, i, j);

// JOptionPane.showMessageDialog(null,("第"+ (i+1) + "行 \n 第"+ (j+1)+ "行 \n 沒有輸入資料" ) );

}

}

pstm.close();

st.close();

conn.close();

}catch (SQLException ex) {

System.out.println(ex.getMessage());

}

JOptionPane.showMessageDialog(null,("資料傳送到資料庫完成" ) );

}

[mysql] download from mysql website and install in windows

Posted by Computer Newbie on 下午6:26 in 學習區, mysql, NetBean, POS系統 / No comments

Mysql website

I download MySQL Community Server 5.5.15 from mysql website.

Install it.

Becare please click Big5 option when end-user connect to server won't cause any problem.

And then create one new user and password.

Create database and table

[java] (13) get data from database and put data into jTable

Posted by Computer Newbie on 晚上10:13 in 學習區, Java, mysql, NetBean, POS系統 / No comments

    private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {

        // TODO add your handling code here:

        Connection conn = null;



        String url = "jdbc:mysql://localhost:3306/sale?useUnicode=true&characterEncoding=big5";

        String user = "testuser";

        String password = "test623";





        try{

           conn = DriverManager.getConnection(url, user, password);

           Statement st = conn.createStatement();





           String Data_Shift = jComboBox1.getSelectedItem().toString();

           String Data_Year = Year_jComboBox2.getSelectedItem().toString();

           String Data_Month = Month_jComboBox3.getSelectedItem().toString();

           String Data_Day   = Day_jComboBox4.getSelectedItem().toString();

//    ResultSet rs = st.executeQuery( "SELECT Balance FROM total_calc where Shift= '早班' and Date= '2011_08_07' "); 要注意裡面有有點 ‘ ’

// ResultSet rs     rs前面要有ResultSet 整行才算完整

           ResultSet rs = st.executeQuery( "SELECT Balance FROM total_calc where Shift ='"+ Data_Shift +"' and Date ='"+ Data_Year   +"-"+ Data_Month +"-"+ Data_Day +"'" );







         // SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';



           int li_row = 0;

           while (rs.next()) {

// 因為rs.getString(1) 1是因為只有抓 Balance 資料，所以放在第一列裡面

//如果是 select * from table 因為抓好幾列資料，所以要指定那一列要顯示

                jTable1.setValueAt(rs.getString(1),li_row,3);

                li_row ++;

           } // while









            rs.close();

            st.close();

            conn.close();

        }catch (SQLException ex) {

         System.out.println(ex.getMessage());

        }

    }

Reference:

Mastering the JTable

How do I determine the column type when querying a database?

[mysql] Data Type in mysql

Posted by Computer Newbie on 清晨7:45 in 學習區, mysql / No comments

MySQL Data Types

In MySQL there are three main types : text, number, and Date/Time types.

Text types:

Data type	Description
CHAR(size)	Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size)	Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
TINYTEXT	Holds a string with a maximum length of 255 characters
TEXT	Holds a string with a maximum length of 65,535 characters
BLOB	For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT	Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB	For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT	Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB	For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.)	Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. Note: The values are sorted in the order you enter them. You enter the possible values in this format: ENUM('X','Y','Z')
SET	Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice

Number types:

Data type	Description
TINYINT(size)	-128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis
SMALLINT(size)	-32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis
MEDIUMINT(size)	-8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis
INT(size)	-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
BIGINT(size)	-9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis
FLOAT(size,d)	A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DOUBLE(size,d)	A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DECIMAL(size,d)	A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

*The integer types have an extra option called UNSIGNED. Normally, the
integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that
range up so it starts at zero instead of a negative number.

Reference:

http://www.w3schools.com/sql/sql_datatypes.asp

[mysql] create new user to mysql

Posted by Computer Newbie on 清晨7:27 in 學習區, mysql, POS系統 / No comments

We connect to the database using the root account. We show all available databases with the SHOW DATABASES statement.


mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.02 sec)

We create a new testdb database. We will use this database throughout the tutorial.


mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623';
Query OK, 0 rows affected (0.00 sec)
mysql> USE testdb;
Database changed
mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> quit;
Bye

We create a new database user. We grant all privileges to this user for all tables of the testdb database.

[java] (6) press button jump to another form

Posted by Computer Newbie on 凌晨1:14 in 學習區, Java, mysql, Ubuntu / No comments

A 程式：

B 程式：

/*

* To change this template, choose Tools | Templates

* and open the template in the editor.

*/

/*

* test2.java

*

* Created on 2011/7/11, 下午 11:39:40

*/

package test;

import java.sql.*;

/**

*

* @author luke

*/

public class test31 extends javax.swing.JFrame {

/** Creates new form test2 */

public test31() {

initComponents();

}

/** This method is called from within the constructor to

* initialize the form.

* WARNING: Do NOT modify this code. The content of this method is

* always regenerated by the Form Editor.

*/

@SuppressWarnings("unchecked")

//

private void initComponents() {

data_TextField1 = new javax.swing.JTextField();

jButton1 = new javax.swing.JButton();

jLabel1 = new javax.swing.JLabel();

jLabel2 = new javax.swing.JLabel();

Sale_jTextField1 = new javax.swing.JTextField();

jLabel3 = new javax.swing.JLabel();

Total_money_jTextField2 = new javax.swing.JTextField();

setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

data_TextField1.addActionListener(new java.awt.event.ActionListener() {

public void actionPerformed(java.awt.event.ActionEvent evt) {

data_TextField1ActionPerformed(evt);

}

});

jButton1.setText("送出");

jButton1.addActionListener(new java.awt.event.ActionListener() {

public void actionPerformed(java.awt.event.ActionEvent evt) {

jButton1ActionPerformed(evt);

}

});

jLabel1.setText("貨名");

jLabel2.setText("銷貨");

jLabel3.setText("小計");

javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());

getContentPane().setLayout(layout);

layout.setHorizontalGroup(

layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)

.addGroup(layout.createSequentialGroup()

.addContainerGap()

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)

.addGroup(layout.createSequentialGroup()

.addComponent(jLabel3, 0, 0, Short.MAX_VALUE)

.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)

.addComponent(Total_money_jTextField2, javax.swing.GroupLayout.PREFERRED_SIZE, 61, javax.swing.GroupLayout.PREFERRED_SIZE)

.addGap(58, 58, 58))

.addGroup(layout.createSequentialGroup()

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)

.addComponent(jLabel2, javax.swing.GroupLayout.Alignment.LEADING, 0, 0, Short.MAX_VALUE)

.addComponent(jLabel1, javax.swing.GroupLayout.Alignment.LEADING))

.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)

.addComponent(data_TextField1, javax.swing.GroupLayout.PREFERRED_SIZE, 79, javax.swing.GroupLayout.PREFERRED_SIZE)

.addComponent(Sale_jTextField1, javax.swing.GroupLayout.PREFERRED_SIZE, 61, javax.swing.GroupLayout.PREFERRED_SIZE))

.addGap(40, 40, 40)))

.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)

.addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 80, javax.swing.GroupLayout.PREFERRED_SIZE)

.addGap(44, 44, 44))

);

layout.setVerticalGroup(

layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)

.addGroup(layout.createSequentialGroup()

.addContainerGap()

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)

.addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE)

.addComponent(data_TextField1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))

.addGap(18, 18, 18)

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)

.addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 26, javax.swing.GroupLayout.PREFERRED_SIZE)

.addComponent(Sale_jTextField1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))

.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)

.addComponent(jLabel3, javax.swing.GroupLayout.PREFERRED_SIZE, 26, javax.swing.GroupLayout.PREFERRED_SIZE)

.addComponent(Total_money_jTextField2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)

.addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 30, javax.swing.GroupLayout.PREFERRED_SIZE))

.addContainerGap())

);

pack();

}//

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {

// TODO add your handling code here:

Connection conn = null;

String url = "jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=big5";

String user = "testuser";

String password = "test623";

try {

conn = DriverManager.getConnection(url, user, password);

Statement st = conn.createStatement();

String Data_text = data_TextField1.getText();

String Sale_text = Sale_jTextField1.getText();

// 如果 Sale_jTextField1 沒有輸入數值的話的判斷式

if ( Sale_text.length() == 0 || Sale_text == null ){

Sale_text = Integer.toString(0);

}

// st.executeUpdate("INSERT INTO tabaco (Name,Sale) VALUES ('"+ Data_text+"','"+ Sale_text +"')");

//去資料庫抓原有庫存數量

ResultSet rs = st.executeQuery("SELECT Upper_stack from tabaco where Name = '" + Data_text + "'");

String SQ = "0";

if(rs.next()){

SQ =rs.getString("Upper_stack");

}

String happy = String.valueOf(Integer.parseInt(SQ)*40);

Total_money_jTextField2.setText(happy);

data_TextField1.setText("");

Sale_jTextField1.setText("");

st.close();

conn.close();

} catch (SQLException ex) {

System.out.println(ex.getMessage());

}

}

private void data_TextField1ActionPerformed(java.awt.event.ActionEvent evt) {

// TODO add your handling code here:

}

/**

* @param args the command line arguments

*/

public static void main(String args[]) {

java.awt.EventQueue.invokeLater(new Runnable() {

public void run() {

new test31().setVisible(true);

}

});

}

// Variables declaration - do not modify

private javax.swing.JTextField Sale_jTextField1;

private javax.swing.JTextField Total_money_jTextField2;

private javax.swing.JTextField data_TextField1;

private javax.swing.JButton jButton1;

private javax.swing.JLabel jLabel1;

private javax.swing.JLabel jLabel2;

private javax.swing.JLabel jLabel3;

// End of variables declaration

}

Reference :

http://www.roseindia.net/java/example/java/swing/login-form-swing.shtml

[mysql] Sketch of my database

Posted by Computer Newbie on 凌晨12:15 in Java, mysql, POS系統, Ubuntu / No comments

Database : sale

Table : sale_tabaco

日期	貨物	單價	上存貨	結存	銷貨	金額
Date	Name	Price	Upper_stack	Final_stack	Sale	Money

Version 1:

CREATE TABLE sale_tabaco (Sale_date date , Name varchar(20), Price int, Upper_stack int, Final_stack int, Sale int, Money int );

disadvantage:

1.too many column. If I input one or two data into this table, other column will display NULL.

Version 2:

Two table (sale_tabaco_name , sale_tabaco_amount)

Create table sale_tabaco_name ( Id INT PRIMARY KEY AUTO_INCREMENT, Name varchar(20), Price int);

Create table sale_tabaco_amount ( Sale_date date , Upper_stack int, Final_stack int, Sale int, Money int );

Version 3:

version 2

Change sale_tabaco_amount

I want add Name varchar(20) into sale_tabaco_amount

Now sale_tabaco_amount and sale_tabaco_name have relation of each other.

Create table sale_tabaco_name ( Id INT PRIMARY KEY AUTO_INCREMENT, Name varchar(20), Price int);

Create table sale_tabaco_amount ( Sale_date date ,Name varchar(20), Upper_stack int, Final_stack int, Sale int, Money int );

Version 4: (2011_08_06)

  CREATE TABLE total_calc(
  Goods_name varchar(20),
  Unit_price int,
  Stocks int,
  Good_in_stock int,
  Ship int,
  Balance int,
  Selling_goods int,
  Subtotal int,
  Space_reserve varchar(100),  Date Date
);    
Goods_name 貨名，Unit_price 單價，Stocks 庫存，Good_in_stock 上存貨，Ship 進貨，Balance 結存，
Selling_goods 銷貨，Subtotal 小計，Space_reserve 備註

Version 5: (2011_08_07)

  CREATE TABLE total_calc(
  Shift varchar(100) CHARACTER SET BIG5,
  Goods_name varchar(20) CHARACTER SET BIG5,
  Unit_price int,
  Stocks int,
  Good_in_stock int,
  Ship int,
  Balance int,
  Selling_goods int,
  Subtotal int,
  Space_reserve varchar(100) CHARACTER SET BIG5,  Date Date
);    
Shift 班表,Goods_name 貨名，Unit_price 單價，Stocks 庫存，Good_in_stock 上存貨，Ship 進貨，Balance 結存，
Selling_goods 銷貨，Subtotal 小計，Space_reserve 備註 Date 日期
新增一個班表，來表示這班是誰做的。

Version 5 - 1 : (2011_08_09)

CREATE TABLE tabaco_product(
  Tabaco_order int,
  Tabaco_name varchar(20),
  Tabaco_unit_price int
);

新增一個 tabaco 產品的資料表。

Version 5 - 2 : (2011_08_09)

CREATE TABLE beverage_product(
  Beverage_order int,
  Beverage_name varchar(20),
  Beverage_unit_price int
);

新增一個 beverage 產品的資料表。

Reference:

http://zetcode.com/databases/mysqljavatutorial/
the data of JTable put into database(mysql)

[java] (5) catch data from database

Posted by Computer Newbie on 晚上11:50 in 學習區, Java, mysql, POS系統, Ubuntu / No comments

/*

* To change this template, choose Tools | Templates

* and open the template in the editor.

*/

/*

* test2.java

*

* Created on 2011/7/11, 下午 11:39:40

*/

package test;

import java.sql.*;

/**

*

* @author luke

*/

public class test31 extends javax.swing.JFrame {

/** Creates new form test2 */

public test31() {

initComponents();

}

/** This method is called from within the constructor to

* initialize the form.

* WARNING: Do NOT modify this code. The content of this method is

* always regenerated by the Form Editor.

*/

@SuppressWarnings("unchecked")

//

private void initComponents() {

data_TextField1 = new javax.swing.JTextField();

jButton1 = new javax.swing.JButton();

jLabel1 = new javax.swing.JLabel();

jLabel2 = new javax.swing.JLabel();

Sale_jTextField1 = new javax.swing.JTextField();

jLabel3 = new javax.swing.JLabel();

Total_money_jTextField2 = new javax.swing.JTextField();

setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

data_TextField1.addActionListener(new java.awt.event.ActionListener() {

public void actionPerformed(java.awt.event.ActionEvent evt) {

data_TextField1ActionPerformed(evt);

}

});

jButton1.setText("送出");

jButton1.addActionListener(new java.awt.event.ActionListener() {

public void actionPerformed(java.awt.event.ActionEvent evt) {

jButton1ActionPerformed(evt);

}

});

jLabel1.setText("貨名");

jLabel2.setText("銷貨");

jLabel3.setText("小計");

javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());

getContentPane().setLayout(layout);

layout.setHorizontalGroup(

layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)

.addGroup(layout.createSequentialGroup()

.addContainerGap()

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)

.addGroup(layout.createSequentialGroup()

.addComponent(jLabel3, 0, 0, Short.MAX_VALUE)

.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)

.addComponent(Total_money_jTextField2, javax.swing.GroupLayout.PREFERRED_SIZE, 61, javax.swing.GroupLayout.PREFERRED_SIZE)

.addGap(58, 58, 58))

.addGroup(layout.createSequentialGroup()

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)

.addComponent(jLabel2, javax.swing.GroupLayout.Alignment.LEADING, 0, 0, Short.MAX_VALUE)

.addComponent(jLabel1, javax.swing.GroupLayout.Alignment.LEADING))

.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)

.addComponent(data_TextField1, javax.swing.GroupLayout.PREFERRED_SIZE, 79, javax.swing.GroupLayout.PREFERRED_SIZE)

.addComponent(Sale_jTextField1, javax.swing.GroupLayout.PREFERRED_SIZE, 61, javax.swing.GroupLayout.PREFERRED_SIZE))

.addGap(40, 40, 40)))

.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)

.addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 80, javax.swing.GroupLayout.PREFERRED_SIZE)

.addGap(44, 44, 44))

);

layout.setVerticalGroup(

layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)

.addGroup(layout.createSequentialGroup()

.addContainerGap()

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)

.addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE)

.addComponent(data_TextField1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))

.addGap(18, 18, 18)

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)

.addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 26, javax.swing.GroupLayout.PREFERRED_SIZE)

.addComponent(Sale_jTextField1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))

.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)

.addComponent(jLabel3, javax.swing.GroupLayout.PREFERRED_SIZE, 26, javax.swing.GroupLayout.PREFERRED_SIZE)

.addComponent(Total_money_jTextField2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)

.addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 30, javax.swing.GroupLayout.PREFERRED_SIZE))

.addContainerGap())

);

pack();

}//

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {

// TODO add your handling code here:

Connection conn = null;

String url = "jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=big5";

String user = "testuser";

String password = "test623";

try {

conn = DriverManager.getConnection(url, user, password);

Statement st = conn.createStatement();

String Data_text = data_TextField1.getText();

String Sale_text = Sale_jTextField1.getText();

// 如果 Sale_jTextField1 沒有輸入數值的話的判斷式

if ( Sale_text.length() == 0 || Sale_text == null ){

Sale_text = Integer.toString(0);

}

// st.executeUpdate("INSERT INTO tabaco (Name,Sale) VALUES ('"+ Data_text+"','"+ Sale_text +"')");

//去資料庫抓原有庫存數量

ResultSet rs = st.executeQuery("SELECT Upper_stack from tabaco where Name = '" + Data_text + "'");

String SQ = "0";

if(rs.next()){

SQ =rs.getString("Upper_stack");

}

System.out.println(SQ);

data_TextField1.setText("");

Sale_jTextField1.setText("");

st.close();

conn.close();

} catch (SQLException ex) {

System.out.println(ex.getMessage());

}

}

private void data_TextField1ActionPerformed(java.awt.event.ActionEvent evt) {

// TODO add your handling code here:

}

/**

* @param args the command line arguments

*/

public static void main(String args[]) {

java.awt.EventQueue.invokeLater(new Runnable() {

public void run() {

new test31().setVisible(true);

}

});

}

// Variables declaration - do not modify

private javax.swing.JTextField Sale_jTextField1;

private javax.swing.JTextField Total_money_jTextField2;

private javax.swing.JTextField data_TextField1;

private javax.swing.JButton jButton1;

private javax.swing.JLabel jLabel1;

private javax.swing.JLabel jLabel2;

private javax.swing.JLabel jLabel3;

// End of variables declaration

}

Reference :

http://www.javaworld.com.tw/jute/post/view?bid=6&id=133307&tpg=1&ppg=1&sty=1&age=0#133307

[mysql] CREATE date TABLE examples

Posted by Computer Newbie on 上午8:03 in 學習區, Java, mysql, POS系統, Ubuntu / No comments


mysql> CREATE TABLE example_timestamp (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(100),
cur_timestamp TIMESTAMP(8)
);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO example_timestamp (data)
VALUES ('The time of creation is:');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM example_timestamp;
+----+--------------------------+---------------------+
| id | data                     | cur_timestamp       |
+----+--------------------------+---------------------+
|  1 | The time of creation is: | 2004-12-01 20:37:22 |
+----+--------------------------+---------------------+
1 row in set (0.00 sec)
mysql> UPDATE example_timestamp 
SET data='The current timestamp is: ' 
WHERE id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM example_timestamp;
+----+---------------------------+---------------------+
| id | data                      | cur_timestamp       |
+----+---------------------------+---------------------+
|  1 | The current timestamp is: | 2004-12-01 20:38:55 |
+----+---------------------------+---------------------+
1 row in set (0.01 sec)

method 2:

CREATE TABLE auto_ins
  (
   MySQL_Function VARCHAR(30),
   DateTime DATETIME,
   Date DATE,
   Time TIME,
   Year YEAR,
   TimeStamp TIMESTAMP
  );

Reference :

[mysql] Jtext 判斷有無輸入字串

Posted by Computer Newbie on 凌晨4:57 in 學習區, mysql, NetBean, POS系統 / No comments

String Price_text = Price_jTextField1.getText();

if ( Price_text.length() == 0 || Price_text == null ){

Price_text = Integer.toString(0);

}

Reference :

http://www.javaworld.com.tw/jute/post/view?bid=5&id=152892&sty=1&tpg=22&age=0

[mysql] Creat table

Posted by Computer Newbie on 上午10:26 in 學習區, mysql / No comments

mysql> create table tabaco (Id INT PRIMARY KEY AUTO_INCREMENT,Name char(20),Price int,Upper_stack int,Final_stack int);

以上用[java](3) 無效

mysql> create table tabaco (Id INT PRIMARY KEY AUTO_INCREMENT,Name char(20) character set big5,Price int,Upper_stack int,Final_stack int);

要用以上宣告，重點部份為紅線

Reference:

http://zetcode.com/databases/mysqljavatutorial/
http://dev.mysql.com/doc/refman/5.0/en/faqs-cjk.html 中文亂碼解法( character set big5 出處)

[java] (4) correct Big5 can display on list

Posted by Computer Newbie on 上午10:07 in 學習區, mysql / No comments


  mysql> SET NAMES 'big5'; \\ Use this first

/*

* To change this template, choose Tools | Templates

* and open the template in the editor.

*/

/*

* test2.java

*

* Created on 2011/7/11, 下午 11:39:40

*/

package test;

import java.util.*;

import javax.swing.table.*;

import java.sql.*;

/**

*

* @author luke

*/

public class test21 extends javax.swing.JFrame {

/** Creates new form test2 */

public test21() {

initComponents();

}

/** This method is called from within the constructor to

* initialize the form.

* WARNING: Do NOT modify this code. The content of this method is

* always regenerated by the Form Editor.

*/

@SuppressWarnings("unchecked")

//

private void initComponents() {

jButton1 = new javax.swing.JButton();

jScrollPane1 = new javax.swing.JScrollPane();

data_jTable1 = new javax.swing.JTable();

setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

jButton1.setText("列出");

jButton1.addActionListener(new java.awt.event.ActionListener() {

public void actionPerformed(java.awt.event.ActionEvent evt) {

jButton1ActionPerformed(evt);

}

});

data_jTable1.setModel(new javax.swing.table.DefaultTableModel(

new Object [][] {

{null, null},

{null, null}

},

new String [] {

"ID", "Name"

}

) {

Class[] types = new Class [] {

java.lang.Integer.class, java.lang.String.class

};

boolean[] canEdit = new boolean [] {

false, true

};

public Class getColumnClass(int columnIndex) {

return types [columnIndex];

}

public boolean isCellEditable(int rowIndex, int columnIndex) {

return canEdit [columnIndex];

}

});

jScrollPane1.setViewportView(data_jTable1);

data_jTable1.getColumnModel().getColumn(0).setResizable(false);

javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());

getContentPane().setLayout(layout);

layout.setHorizontalGroup(

layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)

.addGroup(layout.createSequentialGroup()

.addGap(27, 27, 27)

.addComponent(jButton1)

.addGap(31, 31, 31)

.addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 465, Short.MAX_VALUE)

.addGap(204, 204, 204))

);

layout.setVerticalGroup(

layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)

.addGroup(layout.createSequentialGroup()

.addContainerGap()

.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)

.addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 194, Short.MAX_VALUE)

.addComponent(jButton1))

.addGap(220, 220, 220))

);

pack();

}//

// 模組類別 (AbstractTableModel)import javax.swing.table.*;

class MyTableModel extends AbstractTableModel

{

// import java.util.*;

private ArrayList colname;

private ArrayList data;

public MyTableModel(ResultSet rs)

{

try{

// 取得欄位數量

ResultSetMetaData rm = rs.getMetaData();

int cnum = rm.getColumnCount();

colname = new ArrayList(cnum);

// 取得欄位名稱

for(int i=1; i<=cnum; i++){

colname.add(rm.getColumnName(i));

}

// 取得列

data = new ArrayList();

while(rs.next()){

ArrayList rowdata = new ArrayList();

for(int i=1; i<=cnum; i++){

rowdata.add(rs.getObject(i).toString());

}

data.add(rowdata);

}

}

catch(Exception e){

e.printStackTrace();

}

}

public int getRowCount()

{

return data.size();

}

public int getColumnCount()

{

return colname.size();

}

public Object getValueAt(int row, int column)

{

ArrayList rowdata = (ArrayList)data.get(row);

return rowdata.get(column);

}

public String getColumnName(int column)

{

return (String) colname.get(column);

}

}

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {

// TODO add your handling code here:

Connection conn = null;

// ?useUnicode=true&characterEncoding=big5 very important

String url = "jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=big5";

String user = "testuser";

String password = "test623";

try {

conn = DriverManager.getConnection(url, user, password);

Statement st = conn.createStatement();

String qry = "SELECT * FROM tabaco";

ResultSet rs = st.executeQuery(qry);

data_jTable1.setModel(new MyTableModel(rs));

rs.close();

st.close();

conn.close();

} catch (SQLException ex) {

System.out.println(ex.getMessage());

}

}

/**

* @param args the command line arguments

*/

public static void main(String args[]) {

java.awt.EventQueue.invokeLater(new Runnable() {

public void run() {

new test21().setVisible(true);

}

});

}

// Variables declaration - do not modify

private javax.swing.JTable data_jTable1;

private javax.swing.JButton jButton1;

private javax.swing.JScrollPane jScrollPane1;

// End of variables declaration

}

Reference :

http://www.javaworld.com.tw/jute/post/view?bid=21&id=35895&sty=1&tpg=1&age=0

訂閱：文章 (Atom)

Notepad

2017年3月23日星期四

[Node.js][mysql] How to install mysql(mariaDB)

[mysql] How to install mysql(mariaDB)

2014年4月7日星期一

[MySQL] SQL structure example

2012年7月8日星期日

[NetBean][Java] Load mysql-connector-java-5.1.6.jar into BeanNet

2012年6月19日星期二

[POS] POS learning track

2011年8月11日星期四

[java] (16) List all data in jTable from database, Edit Cell and store it

2011年8月9日星期二

[mysql] mysqldump get database from mysql

2011年8月8日星期一

[java] (15) jComboBox connect to mysql

[java] (14) Update the data of mysql with java code (NetBean )

2011年8月7日星期日

[mysql] download from mysql website and install in windows

2011年8月6日星期六

[java] (13) get data from database and put data into jTable

2011年8月1日星期一

[mysql] Data Type in mysql

MySQL Data Types

[mysql] create new user to mysql

2011年7月27日星期三

[java] (6) press button jump to another form

2011年7月26日星期二

[mysql] Sketch of my database

2011年7月25日星期一

[java] (5) catch data from database

[mysql] CREATE date TABLE examples

[mysql] Jtext 判斷有無輸入字串

2011年7月24日星期日

[mysql] Creat table

[java] (4) correct Big5 can display on list

總網頁瀏覽量

Counter

Categories

2017年3月23日 星期四

2014年4月7日 星期一

2012年7月8日 星期日

2012年6月19日 星期二

2011年8月11日 星期四

2011年8月9日 星期二

2011年8月8日 星期一

2011年8月7日 星期日

2011年8月6日 星期六

2011年8月1日 星期一

MySQL Data Types

2011年7月27日 星期三

2011年7月26日 星期二

2011年7月25日 星期一

2011年7月24日 星期日

總網頁瀏覽量

Counter

Categories

2017年3月23日星期四

2014年4月7日星期一

2012年7月8日星期日

2012年6月19日星期二

2011年8月11日星期四

2011年8月9日星期二

2011年8月8日星期一

2011年8月7日星期日

2011年8月6日星期六

2011年8月1日星期一

2011年7月27日星期三

2011年7月26日星期二

2011年7月25日星期一

2011年7月24日星期日